Re: PITR failing to stop before DROP DATABASE

On 11/25/2014 11:01 PM, Tomas Vondra wrote:
> [snip]
>> So you could see the same after crash recovery, but it's a lot easier to
>> reproduce with PITR.
> So we remove the files, and if there happens to be a crash at the right
> moment, it results in a database with a record in pg_database, but no
> directory/files?
>
> Is it possible to cancel the command half-way through, leaving the
> database in an essentially broken state (half the files exists, half is
> already deleted?
>
>
>> 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.

A rename from <oid>/ to <oid>.del/ is indeed atomic ...

... and so DROP DATABASE could become:
- lock database (sic)
- rename dir
- delete from pg_database
- flush commit record to disk
- background? deletion of all files
(the command would not return until this last phase finished)

If we cancel before flushing, the undo (and recovery after a crash)
would just rename the directory back in place.
If we flushed, recovery after re-starting postmaster would simply delete
the remaining files within <oid>.del/ and then rmdir the directory itself.

... since essentially any other possibility will just leave an
inconsistent db around and wastes space unnecesarily.

Just my two cents, of course.

> I'm in favor of modifying DROP DATABASE so that it behaves like DROP
> TABLE. I find it more consistent (two DROP commands should not really do
> such wildly different things).

+1
> ISTM this would allow us to remove the ForceSyncCommit(); and allow DROP
> DATABASE to be executed within a transaction (say, within a management
> script etc.).

Regards,

/ J.L.