Re: PITR failing to stop before DROP DATABASE

Re: Bruce Momjian 2015-03-20 <20150320223549(dot)GZ6317(at)momjian(dot)us>
> > > >So my suggestion for a simple fix would be to make DROP DATABASE
> > > >execute a short fake transaction before it starts deleting files and
> > > >then continue as before. This would serve as a stopping point for
> > > >recovery_target_time to run into. (We could still fix this properly
> > > >later, but this idea seems like a good fix for a practical problem
> > > >that doesn't break anything else.)
> > >
> > > Yeah, seems reasonable.
> >
> > Here's a first shot at a patch. It's not working yet because I think
> > the commit isn't doing anything because no work was done in the
> > transaction yet.
>
> Where are we on this?

I guess my patch could be fixed by forcing it to acquire a transaction
id (SELECT txid_current() or whatever seems suitable), but my insight
into the gory backend details is limited, so it'd be better if someone
with more clue tried to fix it.

Re: Heikki Linnakangas 2014-11-25 <5474B848(dot)3060909(at)vmware(dot)com>
> >db1 is registered in pg_database, but the directory is missing on
> >disk.
>
> Yeah, DROP DATABASE cheats. It deletes all the files first, and commits the
> transaction only after that. There's this comment at the end of dropdb()
> function:
>
> > /*
> > * Force synchronous commit, thus minimizing the window between removal of
> > * the database files and commital of the transaction. If we crash before
> > * committing, we'll have a DB that's gone on disk but still there
> > * according to pg_database, which is not good.
> > */
>
> So you could see the same after crash recovery, but it's a lot easier to
> reproduce with PITR.
>
> This could be fixed by doing DROP DATABASE the same way we do DROP TABLE. At
> the DROP DATABASE command, just memorize the OID of the dropped database,
> but don't delete anything yet. Perform the actual deletion after flushing
> the commit record to disk. But then you would have the opposite problem -
> you might be left with a database that's dropped according to pg_database,
> but the files are still present on disk.

This seems to be the better idea anyway (and was mentioned again when
I talked to Heikki and Andres about it at FOSDEM).

The "opposite" problem wouldn't be so bad I guess - it might be
visible in practise where you could easily clean up later, but the
original problem is pretty bad if you hit it when trying to do PITR
because something bad happened. (And we treat DROP TABLE the same.)

Christoph
--
cb(at)df7cb(dot)de | http://www.df7cb.de/