Hello hackers,
Attached is v6 of the patch to log the target LSN on DROP TABLE,
TRUNCATE TABLE, and DROP DATABASE when the GUC log_object_drops is
enabled (default off). This is based on Dmitry Lebedev's earlier work,
which I have revised and extended.
When a permanent table is dropped or truncated and the transaction
commits, the server logs a message containing the commit LSN. The
patch adds a new variable, XactLastCommitStart, which records
ProcLastRecPtr (the start of the commit WAL record) immediately after
it is written in RecordTransactionCommit(). The logging is handled by
a transaction callback, ensuring it only runs when the transaction
actually commits. Rolled-back operations, including ROLLBACK TO
SAVEPOINT, are discarded and never logged.
Every permanent table that is physically dropped is logged, including
tables dropped by DROP SCHEMA ... CASCADE and child partitions, so
dropping a schema or a partitioned table produces one line per table.
TRUNCATE is handled the same way. DROP DATABASE logs an intermediate
LSN (the WAL insert position at the
time of the drop) instead of the commit LSN.
This LSN provides an exact recovery target. Setting
recovery_target_lsn to this value with recovery_target_inclusive =
false stops recovery just before the commit record, preserving the
table. For example:
LOG: table "public.t" (OID 16384) dropped, lsn=0/01776698
HINT: To recover dropped or truncated tables, use
recovery_target_lsn = '0/01776698' with recovery_target_inclusive =
false.
On Mon, Sep 28, 2026 at 11:37 AM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> 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.
>
The object access hook runs when the DROP executes, before the
transaction commits, so the commit LSN doesn't exist yet at that
point. To log it, an extension would also need a transaction callback
that runs at commit and would have to discard entries from rolled-back
savepoints. That is what this patch does, with a new
XactLastCommitStart variable for the LSN.
> There are a number of cases to consider, pointed out by Jim, such as
> the TEMP table and the UNLOGGED table. [0]
>
Both are filtered out. We check relation persistence and only register
permanent relations (RELPERSISTENCE_PERMANENT). Neither TEMP nor
UNLOGGED tables produce log entries.
Best,
Salma Elsayed
