Re: Complete data erasure - Mailing list pgsql-hackers

On Tue, 4 Feb 2020 at 09:53, tsunakawa.takay@fujitsu.com
<tsunakawa.takay@fujitsu.com> wrote:
>
> From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
> > That's not really what I meant - let me explain. When I said DROP TABLE
> > should do everything as usual, that includes catalog changes. I.e. after
> > the commit there would not be any remaining entries in system catalogs
> > or anything like that.
> >
> > The only thing we'd do differently is that instead of unlinking the
> > relfilenode segments, we'd move the relfilenode to a persistent queue
> > (essentially a regular table used as a queue relfilenodes). The
> > background worker would watch the queue, and when it gets a new
> > relfilenode it'd "delete" the data and then remove the relfilenode from
> > the queue.
> >
> > So essentially others would not be able to even see the (now dropped)
> > object, they could create new object with the same name etc.
>
> That sounds good.  I think we can also follow the way the WAL archiver does its job, instead of using a regular
table. That is, when the transaction that performed DROP TABLE commits, it puts the data files in the "trash bin,"
whichis actually a filesystem directory.  Or, it just renames the data files in the original directory by appending
somesuffix such as ".del".  Then, the background worker scans the trash bin or the data directory to erase the file
contentand delete the file. 
>
> The trash bin mechanism may open up the application for restoring mistakenly dropped tables, a feature like Oracle's
FlashDrop.  The dropping transaction puts the table metadata (system catalog data or DDL) in the trash bin as well as
thedata file. 
>
>
> > I imagine we might provide a way to wait for the deletion to actually
> > complete (can't do that as part of the DROP TABLE, though), so that
> > people can be sure when the data is actually gone (for scripts etc.).
> > A simple function waiting for the queue to get empty might be enough, I
> > guess, but maybe not.
>
> Agreed, because the user should expect the disk space to be available after DROP TABLE has been committed.  Can't we
reallymake the COMMIT to wait for the erasure to complete?  Do we have to use an asynchronous erasure method with a
backgroundworker?  For example, COMMIT performs: 
>
> 1. Writes a commit WAL record, finalizing the system catalog change.
> 2. Puts the data files in the trash bin or renames them.
> 3. Erase the file content and delete the file.  This could take a long time.
> 4. COMMIT replies success to the client.
>
> What is concerned about is that the need to erase and delete the data file would be forgotten if the server crashes
duringstep 3.  If so, postmaster can do the job at startup, just like it deletes temporary files (although it delays
thestartup.) 

Please note that we need to erase files not only when dropping or
truncating tables but also when aborting the transaction that created
a new table. If user wants to sure the data is actually erased they
needs to wait for rollback as well that could be ROLLBACK command by
user or an error during transaction etc.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services