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 <[email protected]>: > On Fri, 8 Nov 2024 at 08:47, Kirk Wolak <[email protected]> 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 > > > > >
log_object_drops.patch
Description: Binary data
