Re: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles

At Wed, 30 Mar 2022 21:07:40 +0200, Pierre Forstmann <pierre(dot)forstmann(at)gmail(dot)com> wrote in
> I can reproduce your issue with your base_recovery.sh with PG 13.6 on Alma
> Linux 8.5.
>
> If I modify your script to display transaction numbers before and after
> DROP DATABASE with:
>
> echo -e $rt `date` "drop the database test ..." $wt
> # Drop DB
> psql -c 'select txid_current();'
> psql -c 'DROP DATABASE test'
> psql -c 'select txid_current();'

This is not a bug, rather a limitation of recovery_target_time.

The script specifies recovery target by time. The target time time is
compared with timestamps embedded in some kinds of WAL record, mainly
COMMIT.

The reason why it works is that the query creates COMMIT
records. Otherwise no timestamp seen since the backup point and before
the DROP record. Thus recovery has no means to stop by the target time
before the DROP.

The WAL for the bad case looks like this (abbreviated and thinned out):

tx: 486, desc: DROP dir 1663/16384
tx: 486, desc: COMMIT 2022-03-31 16:58:05.981976 JST; inval msgs: catcache 21; sync
tx: 0, desc: SWITCH

> 2022-03-31 16:58:11.448 JST [145533] LOG: recovery stopping before commit of transaction 486, time 2022-03-31 16:58:05.981976+09

There's no timestamped WAL record found before the DROP, recovery runs
until the COMMIT record *after* the DROP, so the directory is removed.

If txid_current() is performed, it looks like:

tx: 486, desc: COMMIT 2022-03-31 17:10:25.775619 JST
tx: 487, desc: DELETE off 4 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0 FPW
tx: 487, desc: DROP dir 1663/16384
tx: 487, desc: COMMIT 2022-03-31 17:10:25.794775 JST
tx: 488, desc: COMMIT 2022-03-31 17:10:25.801121 JST
tx: 0, desc: SWITCH

> recovery stopping before commit of transaction 486, time 2022-03-31 17:10:25.775619+09

In this case, revovery stops *before* the DROP.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center