Re: PITR failing to stop before DROP DATABASE

On March 22, 2015 12:17:57 PM GMT+01:00, Christoph Berg <cb(at)df7cb(dot)de> wrote:
>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.

It'd need to do a GetTopTransactionId().

>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).

Actually allowing for things like this was one of the reasons for the commit/abort extensibility stuff I committed a few days ago.

---
Please excuse brevity and formatting - I am writing this on my mobile phone.