On Fri, 8 Nov 2024 at 08:47, Kirk Wolak <wol...@gmail.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
