Re: PoC: Simplify recovery after dropping a table by LOGGING the restore LSN

Subject: Re: PoC: Simplify recovery after dropping a table by LOGGING the
restore LSN

Hi all,

I've implemented an improved version of this feature that addresses
Kirill's concern about logging the wrong LSN [1].

The key difference: instead of logging WAL insert pointer at lock time,
this patch logs the actual commit LSN by extending XactCallback to pass
the commit LSN from the commit record.

Main improvements:
- Logs real commit LSN, not an earlier position
- Handles ROLLBACK correctly (no log for aborted drops)
- Supports SAVEPOINT, ROLLBACK TO, COMMIT AND CHAIN
- New GUC: log_object_drops (default: off)
- Includes DROP DATABASE support
- Comprehensive TAP tests

This makes the LSN reliable for PITR recovery regardless of system load.

Patch attached. Feedback welcome!

[1]
https://www.postgresql.org/message-id/CALdSSPiRDvbuPgZKAB1%2BBDb3Nfe-i3PYkiy%3DScMZ7tU5f0toKQ%40mail.gmail.com
--
Best regards,
Dmitry Lebedev

пт, 28 нояб. 2025 г. в 16:39, Kirill Reshke <reshkekirill(at)gmail(dot)com>:

> On Fri, 8 Nov 2024 at 08:47, Kirk Wolak <wolakk(at)gmail(dot)com> wrote:
> >
> > Hackers,
> > The concept was driven by an all too common support request. A user
> accidentally dropped the wrong table. (this could also be applied to
> dropping a database, etc).
> >
> > If we had the LSN before the drop, this would be easier. So we
> actually log the LSN when the lock is required so that we have an accurate
> LSN and the recovery is much simpler.
> >
> > All we are doing is inserting a simple LOG message:
> > Acquired drop table lock on table <relname>. Restore at <LSN>
>
> This is indeed useful for the number of accidental data loss recovery.
>
> >
> > Comments are appreciated!
> >
> > Should we ALSO consider this for:
> > - DROP DATABASE
> > - TRUNCATE TABLE
> > - DELETE (only when it is without a WHERE clause?)
> > - UPDATE (only when it is without a WHERE clause?)
>
> options 1 & 2 looks sane, but logging lsn for DELETE/UPDATE looks extra.
>
> I am not convinced this change is necessary to be done inside
> PostgreSQL. What stops us from logging all the same inside object
> access hook defined by extension? This way we can define any rule on
> when to log this.
>
> There are a number of cases to consider, pointed out by Jim, such as
> the TEMP table and the UNLOGGED table. [0]
>
> I want to highlight that we are logging the current WAL insert
> pointer, which can be arbitrarily less than the actual LSN of the
> commit record that deletes the table in case of high load. We first
> acquire a deletion lock, then we assemble the xlog record and only
> then we insert this record into the WAL file. So, the log message
> should be something like 'Restore at lsn xxx or later'.
>
> [0]
> https://www.postgresql.org/message-id/3bda6b56-16bd-48fe-8e23-5ef58a6a4e34%40uni-muenster.de
> --
> Best regards,
> Kirill Reshke
>
>
>
>
>