Re: PITR failing to stop before DROP DATABASE

On 11/26/2014 11:19 AM, Christoph Berg wrote:
> 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:
>
> Oh ok. So this is an artifact of the non-transactionality (is this a
> word?) of CREATE DATABASE.

DROP DATABASE. CREATE DATABASE is a different story. It does similar
non-transactional tricks and has similar issues, but it's a completely
different codepath and could be fixed independently of DROP 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.)

Yeah, seems reasonable.

- Heikki