Re: Heikki Linnakangas 2014-11-25 <5474B848.3060909@vmware.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:
Oh ok. So this is an artifact of the non-transactionality (is this a
word?) of CREATE DATABASE.
>
> > /*
> > * 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.
My concern is mostly that "PITR to just before an accidental DROP
DATABASE" is one of the primary use cases for PITR, so it should Just
Work. (I ran into this during a training and had a hard time
explaining why PITR bugs exist :)
I just did another test, and as expected, the problem goes away if I
execute any transaction just before the DROP DATABASE - even a simple
"SELECT txid_current()" is enough.
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.)
Christoph
--
cb@df7cb.de | http://www.df7cb.de/
