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 <[email protected]> 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
From 696a1b186b220975848a5d6832a645efb2c6332a Mon Sep 17 00:00:00 2001
From: Salma Elsayed <[email protected]>
Date: Mon, 28 Sep 2026 16:24:22 +0300
Subject: [PATCH v6] Add LSN logging for DROP and TRUNCATE TABLE operations
---
doc/src/sgml/config.sgml | 73 +-
src/backend/access/transam/xact.c | 3 +
src/backend/access/transam/xlog.c | 1 +
src/backend/catalog/dependency.c | 27 +
src/backend/commands/dbcommands.c | 15 +
src/backend/commands/tablecmds.c | 218 +++++
src/backend/utils/misc/guc_parameters.dat | 7 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/access/xlog.h | 1 +
src/include/commands/tablecmds.h | 3 +
src/include/utils/guc.h | 1 +
src/test/recovery/meson.build | 1 +
src/test/recovery/t/058_drop_table_logging.pl | 806 ++++++++++++++++++
14 files changed, 1157 insertions(+), 1 deletion(-)
create mode 100644 src/test/recovery/t/058_drop_table_logging.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f36fbb6010..064ea3e0b9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8138,7 +8138,6 @@ local0.* /var/log/postgresql
</listitem>
</varlistentry>
-
<varlistentry id="guc-log-duration" xreflabel="log_duration">
<term><varname>log_duration</varname> (<type>boolean</type>)
<indexterm>
@@ -8504,6 +8503,78 @@ log_line_prefix = '%m [%p] %q%u@%d/%a '
</listitem>
</varlistentry>
+ <varlistentry id="guc-log-object-drops" xreflabel="log_object_drops">
+ <term><varname>log_object_drops</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>log_object_drops</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Causes <command>DROP TABLE</command> and <command>TRUNCATE TABLE</command>
+ operations to be logged with their commit LSN (Log Sequence Number). The commit
+ LSN represents the point in the write-ahead log where the operation becomes
+ durable and visible to other transactions. <command>DROP DATABASE</command>
+ operations are also logged, but because they are non-transactional, they log
+ an intermediate LSN rather than a commit LSN.
+ </para>
+
+ <para>
+ Each logged table entry includes the object name, OID, and the commit LSN,
+ while database entries include an intermediate LSN. For example:
+ <programlisting>
+ LOG: table "public.employees" (OID 16384) dropped, lsn=0/015D4A48
+ LOG: table "public.employees" (OID 16384) truncated, lsn=0/015D4A80
+ LOG: database "testdb" (OID 16385) dropped, lsn=0/015D4B20
+ </programlisting>
+ </para>
+
+ <para>
+ Logged operations include: direct <command>DROP TABLE</command> and
+ <command>TRUNCATE TABLE</command> statements, tables dropped or
+ truncated via cascading operations (such as <command>DROP SCHEMA CASCADE</command>
+ or <command>TRUNCATE ... CASCADE</command>), and
+ <command>DROP DATABASE</command> commands. When a partitioned table
+ is dropped or truncated, both the parent partitioned table and all
+ individual child partitions are logged. Temporary and unlogged tables
+ are never logged.
+ </para>
+
+ <para>
+ Table operations that are rolled back (via <command>ROLLBACK</command> or
+ <command>ROLLBACK TO SAVEPOINT</command>) are not logged. However,
+ <command>DROP DATABASE</command> logs its LSN mid-execution, so the log
+ entry will appear even if the command subsequently fails.
+ </para>
+
+ <para>
+ This parameter is useful for tracking and auditing destructive operations,
+ and for coordinating with external systems that monitor the write-ahead log.
+ Note that enabling this option may generate substantial log volume
+ when dropping or truncating schemas, partitioned tables, or databases
+ containing many tables, as each table is logged individually.
+ </para>
+
+ <para>
+ When recovering a dropped database using <literal>recovery_target_lsn</literal>,
+ note that <command>DROP DATABASE</command> writes a non-transactional marker
+ (<literal>datconnlimit = -2</literal>) to <structname>pg_database</structname>
+ before deleting the database files. This marker is replayed during recovery
+ even when recovery stops before the drop commits, causing the recovered
+ database to appear invalid. After promoting the recovered cluster, reset
+ the marker with:
+ <programlisting>
+UPDATE pg_database SET datconnlimit = -1 WHERE datname = '<replaceable>dbname</replaceable>';
+ </programlisting>
+ </para>
+
+ <para>
+ The default is <literal>off</literal>. Only superusers and users with
+ the appropriate <literal>SET</literal> privilege can change this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-parameter-max-length" xreflabel="log_parameter_max_length">
<term><varname>log_parameter_max_length</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 7b67db514e..1807049eac 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -1493,6 +1493,8 @@ RecordTransactionCommit(void)
MyXactFlags,
InvalidTransactionId, NULL /* plain commit */ );
+ XactLastCommitStart = ProcLastRecPtr;
+
if (replorigin)
/* Move LSNs forward for this replication origin */
replorigin_session_advance(replorigin_xact_state.origin_lsn,
@@ -2184,6 +2186,7 @@ StartTransaction(void)
XactIsoLevel = DefaultXactIsoLevel;
forceSyncCommit = false;
MyXactFlags = 0;
+ XactLastCommitStart = InvalidXLogRecPtr;
/*
* reinitialize within-transaction counters
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 9ec0be77ca..34af3d218e 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -260,6 +260,7 @@ static int LocalXLogInsertAllowed = -1;
XLogRecPtr ProcLastRecPtr = InvalidXLogRecPtr;
XLogRecPtr XactLastRecEnd = InvalidXLogRecPtr;
XLogRecPtr XactLastCommitEnd = InvalidXLogRecPtr;
+XLogRecPtr XactLastCommitStart = InvalidXLogRecPtr;
/*
* RedoRecPtr is this backend's local copy of the REDO record pointer
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index e0fe2a477b..b0466851fd 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -74,6 +74,7 @@
#include "commands/publicationcmds.h"
#include "commands/seclabel.h"
#include "commands/sequence.h"
+#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "commands/typecmds.h"
#include "funcapi.h"
@@ -83,6 +84,7 @@
#include "rewrite/rewriteRemove.h"
#include "storage/lmgr.h"
#include "utils/fmgroids.h"
+#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -1388,7 +1390,32 @@ doDeletion(const ObjectAddress *object, int flags)
RemoveAttributeById(object->objectId,
object->objectSubId);
else
+ {
+ /* Log the drop if requested */
+ if (log_object_drops &&
+ !(flags & PERFORM_DELETION_INTERNAL) &&
+ (relKind == RELKIND_RELATION ||
+ relKind == RELKIND_PARTITIONED_TABLE) &&
+ get_rel_persistence(object->objectId) == RELPERSISTENCE_PERMANENT)
+ {
+ char *relname = get_rel_name(object->objectId);
+
+ if (relname != NULL)
+ {
+ char *schemaname = get_namespace_name(get_rel_namespace(object->objectId));
+
+ RegisterDropOrTruncateTable(object->objectId, relname,
+ schemaname ? schemaname : "unknown",
+ false);
+
+ pfree(relname);
+ if (schemaname)
+ pfree(schemaname);
+ }
+ }
+
heap_drop_with_catalog(object->objectId);
+ }
}
/*
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 7e3fc59eaf..3184fd3457 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -29,6 +29,7 @@
#include "access/multixact.h"
#include "access/tableam.h"
#include "access/xact.h"
+#include "access/xlog.h"
#include "access/xloginsert.h"
#include "access/xlogrecovery.h"
#include "access/xlogutils.h"
@@ -1892,6 +1893,20 @@ dropdb(const char *dbname, bool missing_ok, bool force)
CatalogTupleDelete(pgdbrel, &tup->t_self);
heap_freetuple(tup);
+ /* Log LSN after database drop operation completes */
+ if (log_object_drops)
+ {
+ XLogRecPtr current_lsn = GetXLogInsertRecPtr();
+
+ ereport(LOG,
+ (errmsg("database \"%s\" (OID %u) dropped, lsn=%X/%08X",
+ dbname, db_id, LSN_FORMAT_ARGS(current_lsn)),
+ errhint("To recover to the point before this drop, use recovery_target_lsn = '%X/%08X' "
+ "with recovery_target_inclusive = false. See the log_object_drops "
+ "documentation for caveats and required manual cleanup steps.",
+ LSN_FORMAT_ARGS(current_lsn))));
+ }
+
/*
* Drop db-specific replication slots.
*/
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0274d892f2..9c10a461a3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -352,6 +352,206 @@ typedef struct ForeignTruncateInfo
List *rels;
} ForeignTruncateInfo;
+/* Structure to hold DROP TABLE information */
+typedef struct DropOrTruncateTableInfo
+{
+ Oid reloid;
+ char relname[NAMEDATALEN];
+ char schemaname[NAMEDATALEN];
+ SubTransactionId subxid;
+ bool valid;
+ bool is_truncate;
+} DropOrTruncateTableInfo;
+
+/* Per-transaction list of dropped tables */
+static List *pending_drop_tables = NIL;
+static bool drop_table_callback_registered = false;
+
+static void DropTableXactCallback(XactEvent event, void *arg);
+static void DropTableSubXactCallback(SubXactEvent event, SubTransactionId mySubid,
+ SubTransactionId parentSubid, void *arg);
+
+/*
+ * Register a table drop for logging lsn.
+ */
+void
+RegisterDropOrTruncateTable(Oid reloid, const char *relname, const char *schemaname, bool is_truncate)
+{
+ DropOrTruncateTableInfo *info;
+ MemoryContext oldcontext;
+
+ if (!drop_table_callback_registered)
+ {
+ RegisterXactCallback(DropTableXactCallback, NULL);
+ RegisterSubXactCallback(DropTableSubXactCallback, NULL);
+ drop_table_callback_registered = true;
+ }
+
+ oldcontext = MemoryContextSwitchTo(TopTransactionContext);
+
+ info = (DropOrTruncateTableInfo *) palloc(sizeof(DropOrTruncateTableInfo));
+ info->reloid = reloid;
+ strlcpy(info->relname, relname, NAMEDATALEN);
+ strlcpy(info->schemaname, schemaname, NAMEDATALEN);
+ info->subxid = GetCurrentSubTransactionId();
+ info->valid = true;
+ info->is_truncate = is_truncate;
+
+ pending_drop_tables = lappend(pending_drop_tables, info);
+
+ MemoryContextSwitchTo(oldcontext);
+
+}
+
+/*
+ * SubXactCallback - handle ROLLBACK TO SAVEPOINT
+ */
+static void
+DropTableSubXactCallback(SubXactEvent event, SubTransactionId mySubid,
+ SubTransactionId parentSubid, void *arg)
+{
+ ListCell *lc;
+ MemoryContext oldcontext;
+
+ if (pending_drop_tables == NIL)
+ return;
+
+ /*
+ * On subtransaction abort, remove all entries belonging to the aborted
+ * subtransaction and its children.
+ */
+ if (event == SUBXACT_EVENT_ABORT_SUB)
+ {
+ /* Switch to TopTransactionContext for the new list */
+ oldcontext = MemoryContextSwitchTo(TopTransactionContext);
+
+ foreach(lc, pending_drop_tables)
+ {
+ DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc);
+
+ /*
+ * Mark entries that belong to our subtransactions.
+ * SubTransactionIds are assigned incrementally, so we can compare
+ * them.
+ */
+ if (info->subxid >= mySubid)
+ {
+ info->valid = false;
+ }
+ }
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+}
+
+/*
+ * DropTableXactCallback
+ * Transaction callback to log commit LSN for DROP and TRUNCATE TABLE operations.
+ */
+static void
+DropTableXactCallback(XactEvent event, void *arg)
+{
+ ListCell *lc;
+
+ if (pending_drop_tables == NIL)
+ return;
+
+ if (event == XACT_EVENT_COMMIT)
+ {
+ DropOrTruncateTableInfo *last_valid = NULL;
+
+ Assert(!XLogRecPtrIsInvalid(XactLastCommitStart));
+
+ /* Find the last entry that actually committed */
+ foreach(lc, pending_drop_tables)
+ {
+ DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc);
+
+ if (info->valid)
+ last_valid = info;
+ }
+
+ foreach(lc, pending_drop_tables)
+ {
+ DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc);
+
+ if (!info->valid)
+ continue;
+
+ if (info->is_truncate)
+ {
+ ereport(LOG,
+ (errmsg("table \"%s.%s\" (OID %u) truncated, lsn=%X/%08X",
+ info->schemaname, info->relname, info->reloid,
+ LSN_FORMAT_ARGS(XactLastCommitStart)),
+ (info == last_valid) ?
+ errhint("To recover dropped or truncated tables, use "
+ "recovery_target_lsn = '%X/%08X' with "
+ "recovery_target_inclusive = false.",
+ LSN_FORMAT_ARGS(XactLastCommitStart)) : 0));
+ }
+ else
+ {
+ ereport(LOG,
+ (errmsg("table \"%s.%s\" (OID %u) dropped, lsn=%X/%08X",
+ info->schemaname, info->relname, info->reloid,
+ LSN_FORMAT_ARGS(XactLastCommitStart)),
+ (info == last_valid) ?
+ errhint("To recover dropped or truncated tables, use "
+ "recovery_target_lsn = '%X/%08X' with "
+ "recovery_target_inclusive = false.",
+ LSN_FORMAT_ARGS(XactLastCommitStart)) : 0));
+ }
+ }
+ }
+ else if (event == XACT_EVENT_PREPARE)
+ {
+ foreach(lc, pending_drop_tables)
+ {
+ DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc);
+
+ if (!info->valid)
+ continue;
+
+ if (info->is_truncate)
+ {
+ ereport(LOG,
+ (errmsg("table \"%s.%s\" (OID %u) truncated inside a prepared transaction",
+ info->schemaname, info->relname, info->reloid),
+ errdetail("Automatic recovery LSN capture is not supported for two-phase commit."),
+ errhint("If this table needs to be recovered, the WAL around the eventual COMMIT PREPARED will need to be inspected manually.")));
+ }
+ else
+ {
+ ereport(LOG,
+ (errmsg("table \"%s.%s\" (OID %u) dropped inside a prepared transaction",
+ info->schemaname, info->relname, info->reloid),
+ errdetail("Automatic recovery LSN capture is not supported for two-phase commit."),
+ errhint("If this table needs to be recovered, the WAL around the eventual COMMIT PREPARED will need to be inspected manually.")));
+ }
+ }
+ }
+
+ /* Clean up after commit or abort */
+ if (event == XACT_EVENT_COMMIT ||
+ event == XACT_EVENT_ABORT ||
+ event == XACT_EVENT_PARALLEL_ABORT ||
+ event == XACT_EVENT_PREPARE)
+ {
+ /* Free the DropTableInfo structures */
+ foreach(lc, pending_drop_tables)
+ {
+ DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc);
+
+ pfree(info);
+ }
+
+ list_free(pending_drop_tables);
+ pending_drop_tables = NIL;
+ }
+}
+
+
/* Partial or complete FK creation in addFkConstraint() */
typedef enum addFkConstraintSides
{
@@ -2175,6 +2375,24 @@ ExecuteTruncateGuts(List *explicit_rels,
{
Relation rel = (Relation) lfirst(cell);
+ /* Log the truncation for log_object_drops */
+ if (log_object_drops &&
+ (rel->rd_rel->relkind == RELKIND_RELATION ||
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) &&
+ rel->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT)
+ {
+ char *relname = RelationGetRelationName(rel);
+ char *schemaname = get_namespace_name(RelationGetNamespace(rel));
+
+ /* Pass "true" because this is a TRUNCATE, not a DROP */
+ RegisterDropOrTruncateTable(RelationGetRelid(rel), relname,
+ schemaname ? schemaname : "unknown",
+ true);
+
+ if (schemaname)
+ pfree(schemaname);
+ }
+
/* Skip partitioned tables as there is nothing to do */
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
continue;
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index c57441f7d9..14eb9cf635 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -1793,6 +1793,13 @@
assign_hook => 'assign_log_min_messages',
},
+{ name => 'log_object_drops', type => 'bool', context => 'PGC_SUSET', group => 'LOGGING_WHAT',
+ short_desc => 'Logs LSN for DROP TABLE, TRUNCATE TABLE, and DROP DATABASE operations.',
+ long_desc => 'When enabled, the system will log the LSN (Log Sequence Number) whenever a DROP TABLE, TRUNCATE TABLE, or DROP DATABASE command is executed.',
+ variable => 'log_object_drops',
+ boot_val => 'false',
+},
+
{ name => 'log_parameter_max_length', type => 'int', context => 'PGC_SUSET', group => 'LOGGING_WHAT',
short_desc => 'Sets the maximum length in bytes of data logged for bind parameter values when logging statements.',
long_desc => '-1 means log values in full.',
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 342aaeef59..241c6e5154 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -546,6 +546,7 @@ bool Debug_write_read_parse_plan_trees;
bool Debug_raw_expression_coverage_test;
#endif
+bool log_object_drops = false;
bool log_parser_stats = false;
bool log_planner_stats = false;
bool log_executor_stats = false;
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e759f06b50..af2b1c8890 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -672,6 +672,7 @@
#log_lock_failures = off # log lock failures
#log_recovery_conflict_waits = off # log standby recovery conflict waits
# >= deadlock_timeout
+#log_object_drops = off
#log_parameter_max_length = -1 # when logging statements, limit logged
# bind-parameter values to N bytes;
# -1 means print in full, 0 disables
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 7a590b7e1e..ff78341f96 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -32,6 +32,7 @@ extern PGDLLIMPORT int wal_sync_method;
extern PGDLLIMPORT XLogRecPtr ProcLastRecPtr;
extern PGDLLIMPORT XLogRecPtr XactLastRecEnd;
+extern PGDLLIMPORT XLogRecPtr XactLastCommitStart;
extern PGDLLIMPORT XLogRecPtr XactLastCommitEnd;
/* these variables are GUC parameters related to XLOG */
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index c3d8518cb6..5bc66c4e35 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -32,6 +32,9 @@ extern TupleDesc BuildDescForRelation(const List *columns);
extern void RemoveRelations(DropStmt *drop);
+extern void RegisterDropOrTruncateTable(Oid reloid, const char *relname,
+ const char *schemaname, bool is_truncate);
+
extern Oid AlterTableLookupRelation(AlterTableStmt *stmt, LOCKMODE lockmode);
extern void AlterTable(AlterTableStmt *stmt, LOCKMODE lockmode,
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 164efba6b5..473aed59d8 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -305,6 +305,7 @@ extern PGDLLIMPORT int log_statement_max_length;
extern PGDLLIMPORT double log_statement_sample_rate;
extern PGDLLIMPORT double log_xact_sample_rate;
extern PGDLLIMPORT char *backtrace_functions;
+extern PGDLLIMPORT bool log_object_drops;
extern PGDLLIMPORT int temp_file_limit;
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index ebb12dd876..5b855259fd 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -66,6 +66,7 @@ tests += {
't/055_cascade_reconnect.pl',
't/056_standby_snapshot_export.pl',
't/057_snapshot_commit_race.pl',
+ 't/058_drop_table_logging.pl',
],
},
}
diff --git a/src/test/recovery/t/058_drop_table_logging.pl b/src/test/recovery/t/058_drop_table_logging.pl
new file mode 100644
index 0000000000..5ab22c9e7d
--- /dev/null
+++ b/src/test/recovery/t/058_drop_table_logging.pl
@@ -0,0 +1,806 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test DROP TABLE, TRUNCATE TABLE, and DROP DATABASE logging functionality.
+# Verifies that operations are logged with correct LSN values, and that
+# point-in-time recovery (PITR) to the logged LSN actually recovers the data.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize primary node with WAL archiving and streaming enabled for PITR
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init(has_archiving => 1, allows_streaming => 1);
+$node->append_conf('postgresql.conf', qq{
+log_min_messages = log
+logging_collector = off
+log_destination = 'stderr'
+log_object_drops = on
+max_prepared_transactions = 5
+});
+$node->start;
+
+# Track log file position for incremental reading
+my $log_offset = 0;
+
+# Cache for current test - stores log content read once per test
+my $current_test_log_cache = undef;
+
+# Reset to start a new test - clears cache and updates offset
+sub start_new_test
+{
+ my ($test_name) = @_;
+ note($test_name) if defined $test_name;
+
+ $current_test_log_cache = undef;
+
+ # Update offset to current position
+ my $logfile = $node->logfile;
+ $log_offset = -s $logfile;
+}
+
+# Get log content for current test (cached within test)
+sub get_test_log_content
+{
+ return $current_test_log_cache if defined $current_test_log_cache;
+
+ # Read new content since last test started
+ my $logfile = $node->logfile;
+ my $current_size = -s $logfile;
+
+ # If offset is beyond file size, reset to 0
+ $log_offset = 0 if $log_offset > $current_size;
+
+ # Read only new content
+ $current_test_log_cache = slurp_file($logfile, $log_offset);
+
+ return $current_test_log_cache;
+}
+
+# Count matching log entries in current test
+sub count_drop_logs
+{
+ my ($pattern) = @_;
+ my $log = get_test_log_content();
+ my @matches = $log =~ /$pattern/g;
+ return scalar @matches;
+}
+
+# Get matching log lines in current test
+sub get_log_lines
+{
+ my ($pattern) = @_;
+ my $log = get_test_log_content();
+ my @lines = split /\n/, $log;
+ my @matching_lines = grep { /$pattern/ } @lines;
+ return @matching_lines;
+}
+
+# Helper function to extract LSN from log line
+sub extract_lsn
+{
+ my ($line) = @_;
+ if ($line =~ /lsn[:=]\s*([0-9A-Fa-f]+\/[0-9A-Fa-f]+)/i)
+ {
+ return $1;
+ }
+ return undef;
+}
+
+# Build pattern for DROP TABLE log entry
+sub drop_table_pattern
+{
+ my ($schema, $table) = @_;
+ return qr/table "$schema\.$table" \(OID \d+\) dropped/;
+}
+
+# Build pattern for TRUNCATE TABLE log entry
+sub truncate_table_pattern
+{
+ my ($schema, $table) = @_;
+ return qr/table "$schema\.$table" \(OID \d+\) truncated/;
+}
+
+# Build pattern for DROP DATABASE log entry
+sub drop_database_pattern
+{
+ my ($dbname) = @_;
+ return qr/database "$dbname" \(OID \d+\) dropped/;
+}
+
+# Test helper: Execute SQL and verify drop count
+sub test_drop_count
+{
+ my ($test_name, $sql, $pattern, $expected_count) = @_;
+
+ start_new_test($test_name);
+ $node->safe_psql('postgres', $sql);
+
+ my $count = count_drop_logs($pattern);
+ is($count, $expected_count, "$test_name: count check");
+}
+
+# Test helper: Execute SQL and verify log entry exists with valid LSN
+sub test_drop_logged
+{
+ my ($test_name, $sql, $pattern, $extra_checks) = @_;
+
+ start_new_test($test_name);
+ $node->safe_psql('postgres', $sql);
+
+ my @log_lines = get_log_lines($pattern);
+ is(scalar @log_lines, 1, "$test_name: entry logged");
+
+ if (@log_lines)
+ {
+ like($log_lines[0], qr/lsn[:=]\s*[0-9A-Fa-f]+\/[0-9A-Fa-f]+/i,
+ "$test_name: LSN present");
+ unlike($log_lines[0], qr/lsn[:=]\s*0\/0\b/i,
+ "$test_name: LSN not invalid");
+
+ # Execute additional checks if provided
+ $extra_checks->($log_lines[0]) if defined $extra_checks;
+ }
+}
+
+# Test helper: Execute SQL and verify nothing was logged
+sub test_drop_not_logged
+{
+ my ($test_name, $sql, $pattern) = @_;
+
+ start_new_test($test_name);
+ $node->safe_psql('postgres', $sql);
+
+ my $count = count_drop_logs($pattern);
+ is($count, 0, "$test_name: not logged");
+}
+
+# Test helper: Verify multiple drops in one transaction
+sub test_multiple_drops
+{
+ my ($test_name, $sql, @table_specs) = @_;
+
+ start_new_test($test_name);
+ $node->safe_psql('postgres', $sql);
+
+ foreach my $spec (@table_specs)
+ {
+ my ($schema, $table, $expected) = @$spec;
+ my $count = count_drop_logs(drop_table_pattern($schema, $table));
+ is($count, $expected, "$test_name: $schema.$table");
+ }
+}
+
+# Test helper: Verify multiple truncates in one transaction
+sub test_multiple_truncates
+{
+ my ($test_name, $sql, @table_specs) = @_;
+
+ start_new_test($test_name);
+ $node->safe_psql('postgres', $sql);
+
+ foreach my $spec (@table_specs)
+ {
+ my ($schema, $table, $expected) = @$spec;
+ my $count = count_drop_logs(truncate_table_pattern($schema, $table));
+ is($count, $expected, "$test_name: $schema.$table");
+ }
+}
+
+# ==============================================================================
+# PITR Tests: Verify that recovering to the logged LSN actually restores data
+# ==============================================================================
+
+# Take a base backup before testing PITR
+$node->backup('bkp');
+
+# PITR Test 1: DROP TABLE recovery
+start_new_test('PITR recovery for DROP TABLE');
+$node->safe_psql('postgres', q{
+ CREATE TABLE pitr_drop_table (id int, val text);
+ INSERT INTO pitr_drop_table VALUES (1, 'alpha'), (2, 'beta');
+});
+$node->safe_psql('postgres', q{
+ DROP TABLE pitr_drop_table;
+});
+my @drop_lines = get_log_lines(drop_table_pattern('public', 'pitr_drop_table'));
+is(scalar @drop_lines, 1, 'PITR: DROP TABLE logged');
+my $drop_lsn = extract_lsn($drop_lines[0]);
+ok(defined $drop_lsn, "PITR: Scraped drop commit LSN: $drop_lsn");
+
+# Switch WAL to make sure the commit record is archived
+my $walfile_drop = $node->safe_psql('postgres',
+ "SELECT pg_walfile_name(pg_current_wal_lsn());");
+$node->safe_psql('postgres', "SELECT pg_switch_wal();");
+$node->poll_query_until('postgres',
+ "SELECT '$walfile_drop' <= last_archived_wal FROM pg_stat_archiver;")
+ or die "Timed out waiting for WAL archival";
+
+# Restore a standby node to the scraped LSN with recovery_target_inclusive = false
+my $node_pitr_drop = PostgreSQL::Test::Cluster->new('pitr_drop');
+$node_pitr_drop->init_from_backup($node, 'bkp', has_restoring => 1, standby => 0);
+$node_pitr_drop->append_conf('postgresql.conf', qq{
+recovery_target_lsn = '$drop_lsn'
+recovery_target_inclusive = false
+recovery_target_action = 'promote'
+});
+$node_pitr_drop->start;
+$node_pitr_drop->poll_query_until('postgres', "SELECT pg_is_in_recovery() = 'f';")
+ or die "Timed out waiting for PITR promotion after DROP TABLE";
+
+my $drop_count = $node_pitr_drop->safe_psql('postgres',
+ "SELECT count(*) FROM pitr_drop_table;");
+is($drop_count, '2', 'PITR DROP: Table exists with all 2 rows restored');
+my $drop_vals = $node_pitr_drop->safe_psql('postgres',
+ "SELECT string_agg(val, ',' ORDER BY id) FROM pitr_drop_table;");
+is($drop_vals, 'alpha,beta', 'PITR DROP: Table row contents match exactly');
+$node_pitr_drop->teardown_node;
+
+
+# PITR Test 2: TRUNCATE TABLE recovery
+start_new_test('PITR recovery for TRUNCATE TABLE');
+$node->safe_psql('postgres', q{
+ CREATE TABLE pitr_trunc_table (id int, val text);
+ INSERT INTO pitr_trunc_table VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');
+});
+$node->safe_psql('postgres', q{
+ TRUNCATE TABLE pitr_trunc_table;
+});
+my @trunc_lines = get_log_lines(truncate_table_pattern('public', 'pitr_trunc_table'));
+is(scalar @trunc_lines, 1, 'PITR: TRUNCATE TABLE logged');
+my $trunc_lsn = extract_lsn($trunc_lines[0]);
+ok(defined $trunc_lsn, "PITR: Scraped truncate commit LSN: $trunc_lsn");
+
+# Switch WAL to make sure the commit record is archived
+my $walfile_trunc = $node->safe_psql('postgres',
+ "SELECT pg_walfile_name(pg_current_wal_lsn());");
+$node->safe_psql('postgres', "SELECT pg_switch_wal();");
+$node->poll_query_until('postgres',
+ "SELECT '$walfile_trunc' <= last_archived_wal FROM pg_stat_archiver;")
+ or die "Timed out waiting for WAL archival";
+
+# Restore a standby node to the scraped LSN with recovery_target_inclusive = false
+my $node_pitr_trunc = PostgreSQL::Test::Cluster->new('pitr_trunc');
+$node_pitr_trunc->init_from_backup($node, 'bkp', has_restoring => 1, standby => 0);
+$node_pitr_trunc->append_conf('postgresql.conf', qq{
+recovery_target_lsn = '$trunc_lsn'
+recovery_target_inclusive = false
+recovery_target_action = 'promote'
+});
+$node_pitr_trunc->start;
+$node_pitr_trunc->poll_query_until('postgres', "SELECT pg_is_in_recovery() = 'f';")
+ or die "Timed out waiting for PITR promotion after TRUNCATE TABLE";
+
+my $trunc_count = $node_pitr_trunc->safe_psql('postgres',
+ "SELECT count(*) FROM pitr_trunc_table;");
+is($trunc_count, '3', 'PITR TRUNCATE: All 3 rows preserved');
+my $trunc_vals = $node_pitr_trunc->safe_psql('postgres',
+ "SELECT string_agg(val, ',' ORDER BY id) FROM pitr_trunc_table;");
+is($trunc_vals, 'foo,bar,baz', 'PITR TRUNCATE: Table row contents match exactly');
+$node_pitr_trunc->teardown_node;
+
+
+# ==============================================================================
+# Functional Tests
+# ==============================================================================
+
+# Test 1: Single statement DROP TABLE
+test_drop_logged(
+ 'Test 1: Simple DROP TABLE',
+ q{
+ CREATE TABLE test_simple (id int);
+ INSERT INTO test_simple VALUES (1);
+ DROP TABLE test_simple;
+ },
+ drop_table_pattern('public', 'test_simple')
+);
+
+# Test 2: DROP TABLE inside transaction block
+test_drop_logged(
+ 'Test 2: DROP TABLE in transaction',
+ q{
+ CREATE TABLE test_in_xact (id int);
+ INSERT INTO test_in_xact VALUES (1);
+ BEGIN;
+ DROP TABLE test_in_xact;
+ COMMIT;
+ },
+ drop_table_pattern('public', 'test_in_xact')
+);
+
+# Test 3a: ROLLBACK of DROP TABLE (should NOT be logged)
+test_drop_not_logged(
+ 'Test 3a: Rolled back DROP not logged',
+ q{
+ CREATE TABLE test_rollback (id int);
+ INSERT INTO test_rollback VALUES (1);
+ BEGIN;
+ DROP TABLE test_rollback;
+ ROLLBACK;
+ },
+ drop_table_pattern('public', 'test_rollback')
+);
+
+# Test 3b: Committed DROP after rollback
+test_drop_count(
+ 'Test 3b: Committed DROP logged',
+ q{
+ SELECT * FROM test_rollback;
+ DROP TABLE test_rollback;
+ },
+ drop_table_pattern('public', 'test_rollback'),
+ 1
+);
+
+# Test 4: DROP SCHEMA CASCADE - all tables logged
+test_multiple_drops(
+ 'Test 4: DROP SCHEMA CASCADE - all tables logged',
+ q{
+ CREATE SCHEMA test_schema;
+ CREATE TABLE test_schema.table1 (id int);
+ CREATE TABLE test_schema.table2 (name text);
+ INSERT INTO test_schema.table1 VALUES (1);
+ INSERT INTO test_schema.table2 VALUES ('test');
+ BEGIN;
+ DROP SCHEMA test_schema CASCADE;
+ COMMIT;
+ },
+ ['test_schema', 'table1', 1],
+ ['test_schema', 'table2', 1]
+);
+
+# Test 5: DROP TABLE with FK CASCADE - only explicitly dropped table is logged
+test_drop_count(
+ 'Test 5: DROP TABLE CASCADE with foreign keys',
+ q{
+ CREATE TABLE test_parent (id int PRIMARY KEY);
+ CREATE TABLE test_child (id int, parent_id int REFERENCES test_parent(id));
+ INSERT INTO test_parent VALUES (1);
+ INSERT INTO test_child VALUES (1, 1);
+ BEGIN;
+ DROP TABLE test_parent CASCADE;
+ COMMIT;
+ },
+ drop_table_pattern('public', 'test_parent'),
+ 1
+);
+
+# Test 6: Multiple DROP TABLE in single statement
+test_multiple_drops(
+ 'Test 6: Multiple tables in single DROP statement',
+ q{
+ CREATE TABLE test_multi1 (id int);
+ CREATE TABLE test_multi2 (id int);
+ CREATE TABLE test_multi3 (id int);
+ BEGIN;
+ DROP TABLE test_multi1, test_multi2, test_multi3;
+ COMMIT;
+ },
+ ['public', 'test_multi1', 1],
+ ['public', 'test_multi2', 1],
+ ['public', 'test_multi3', 1]
+);
+my @hint_lines = get_log_lines(qr/HINT:\s+To recover dropped or truncated tables/);
+is(scalar @hint_lines, 1, 'Test 6: Exactly one hint emitted for multiple table drops');
+
+# Test 7: DROP PARTITIONED TABLE - all tables logged
+test_multiple_drops(
+ 'Test 7: Partitioned table and partitions',
+ q{
+ CREATE TABLE test_partitioned (id int, created_at date) PARTITION BY RANGE (created_at);
+ CREATE TABLE test_part_2024 PARTITION OF test_partitioned
+ FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
+ CREATE TABLE test_part_2025 PARTITION OF test_partitioned
+ FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
+ BEGIN;
+ DROP TABLE test_partitioned CASCADE;
+ COMMIT;
+ },
+ ['public', 'test_partitioned', 1],
+ ['public', 'test_part_2024', 1],
+ ['public', 'test_part_2025', 1]
+);
+
+# Test 7b: Explicit DROP of single child partition directly is logged
+test_drop_logged(
+ 'Test 7b: Direct DROP of child partition',
+ q{
+ CREATE TABLE test_part_p2 (id int) PARTITION BY RANGE (id);
+ CREATE TABLE test_part_c1 PARTITION OF test_part_p2 FOR VALUES FROM (1) TO (10);
+ CREATE TABLE test_part_c2 PARTITION OF test_part_p2 FOR VALUES FROM (10) TO (20);
+ DROP TABLE test_part_c1;
+ DROP TABLE test_part_p2;
+ },
+ drop_table_pattern('public', 'test_part_c1')
+);
+
+# Test 7c: DROP SCHEMA CASCADE with partition whose parent is in another schema
+test_multiple_drops(
+ 'Test 7c: DROP SCHEMA with external partition',
+ q{
+ CREATE SCHEMA schema_parent;
+ CREATE SCHEMA schema_part;
+ CREATE TABLE schema_parent.parent (id int) PARTITION BY RANGE (id);
+ CREATE TABLE schema_part.part1 PARTITION OF schema_parent.parent FOR VALUES FROM (1) TO (10);
+ BEGIN;
+ DROP SCHEMA schema_part CASCADE;
+ COMMIT;
+ DROP TABLE schema_parent.parent;
+ },
+ ['schema_part', 'part1', 1]
+);
+
+# Test 8: Mixed operations in one transaction
+test_multiple_drops(
+ 'Test 8: Mixed CREATE and DROP operations in transaction',
+ q{
+ CREATE SCHEMA mixed_schema;
+ BEGIN;
+ CREATE TABLE mixed_schema.table1 (id int);
+ INSERT INTO mixed_schema.table1 VALUES (1);
+ CREATE TABLE mixed_schema.table2 (id int);
+ DROP TABLE mixed_schema.table2;
+ CREATE TABLE outside_table (id int);
+ INSERT INTO outside_table VALUES (1);
+ DROP SCHEMA mixed_schema CASCADE;
+ DROP TABLE outside_table;
+ COMMIT;
+ },
+ ['mixed_schema', 'table1', 1],
+ ['mixed_schema', 'table2', 1],
+ ['public', 'outside_table', 1]
+);
+
+# Test 9: Disabled GUC (log_object_drops = off)
+test_drop_not_logged(
+ 'Test 9: log_object_drops = off does not log',
+ q{
+ SET log_object_drops = off;
+ CREATE TABLE test_guc_disabled (id int);
+ DROP TABLE test_guc_disabled;
+ SET log_object_drops = on;
+ },
+ drop_table_pattern('public', 'test_guc_disabled')
+);
+
+# Test 10: DROP temporary table (should NOT be logged)
+test_drop_not_logged(
+ 'Test 10: Temporary table',
+ q{
+ CREATE TEMP TABLE test_temp (id int);
+ INSERT INTO test_temp VALUES (1);
+ BEGIN;
+ DROP TABLE test_temp;
+ COMMIT;
+ },
+ drop_table_pattern('pg_temp', 'test_temp')
+);
+
+# Test 11: DROP UNLOGGED table (should NOT be logged)
+test_drop_not_logged(
+ 'Test 11: UNLOGGED table',
+ q{
+ CREATE UNLOGGED TABLE test_unlogged (id int);
+ INSERT INTO test_unlogged VALUES (1);
+ BEGIN;
+ DROP TABLE test_unlogged;
+ COMMIT;
+ },
+ drop_table_pattern('public', 'test_unlogged')
+);
+
+# Test 12: DROP VIEW (should NOT be logged)
+test_drop_not_logged(
+ 'Test 12: VIEW drop not logged',
+ q{
+ CREATE TABLE test_view_base (id int);
+ CREATE VIEW test_view AS SELECT * FROM test_view_base;
+ DROP VIEW test_view;
+ DROP TABLE test_view_base;
+ },
+ qr/table ".*test_view" \(OID \d+\) dropped/
+);
+
+# Test 13: DROP MATERIALIZED VIEW (should NOT be logged)
+test_drop_not_logged(
+ 'Test 13: MATERIALIZED VIEW drop not logged',
+ q{
+ CREATE MATERIALIZED VIEW test_matview AS SELECT 1 AS a;
+ DROP MATERIALIZED VIEW test_matview;
+ },
+ qr/table ".*test_matview" \(OID \d+\) dropped/
+);
+
+# Test 14: DROP INDEX (should NOT be logged)
+test_drop_not_logged(
+ 'Test 14: INDEX drop not logged',
+ q{
+ CREATE TABLE test_index_table (id int);
+ CREATE INDEX test_idx ON test_index_table(id);
+ DROP INDEX test_idx;
+ DROP TABLE test_index_table;
+ },
+ qr/table ".*test_idx" \(OID \d+\) dropped/
+);
+
+# Test 15: Table rewrite (ALTER TABLE ... ALTER COLUMN TYPE)
+test_drop_not_logged(
+ 'Test 15: Table rewrite does not produce false DROP TABLE log',
+ q{
+ CREATE TABLE test_rewrite (id int);
+ INSERT INTO test_rewrite VALUES (1);
+ ALTER TABLE test_rewrite ALTER COLUMN id TYPE bigint;
+ DROP TABLE test_rewrite;
+ },
+ qr/table ".*pg_temp.*" \(OID \d+\) dropped/
+);
+
+# Test 16: Table inheritance hierarchy - all tables logged
+test_multiple_drops(
+ 'Test 16: Inheritance hierarchy',
+ q{
+ CREATE TABLE parent_inherit (id int);
+ CREATE TABLE child_inherit1 () INHERITS (parent_inherit);
+ CREATE TABLE child_inherit2 () INHERITS (parent_inherit);
+ INSERT INTO parent_inherit VALUES (1);
+ INSERT INTO child_inherit1 VALUES (2);
+ INSERT INTO child_inherit2 VALUES (3);
+ BEGIN;
+ DROP TABLE parent_inherit CASCADE;
+ COMMIT;
+ },
+ ['public', 'parent_inherit', 1],
+ ['public', 'child_inherit1', 1],
+ ['public', 'child_inherit2', 1]
+);
+
+# Test 17: SAVEPOINT and ROLLBACK TO
+start_new_test('Test 17: SAVEPOINT and ROLLBACK TO');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_savepoint1 (id int);
+ CREATE TABLE test_savepoint2 (id int);
+ CREATE TABLE test_savepoint3 (id int);
+ BEGIN;
+ DROP TABLE test_savepoint1;
+ SAVEPOINT sp1;
+ DROP TABLE test_savepoint2;
+ ROLLBACK TO sp1;
+ DROP TABLE test_savepoint3;
+ COMMIT;
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_savepoint1')), 1, 'Test 17: sp1 before savepoint');
+is(count_drop_logs(drop_table_pattern('public', 'test_savepoint2')), 0, 'Test 17: sp2 rolled back');
+is(count_drop_logs(drop_table_pattern('public', 'test_savepoint3')), 1, 'Test 17: sp3 after rollback');
+$node->safe_psql('postgres', 'DROP TABLE IF EXISTS test_savepoint2;');
+
+# Test 18: SAVEPOINT and RELEASE
+start_new_test('Test 18: SAVEPOINT and RELEASE');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_release1 (id int);
+ CREATE TABLE test_release2 (id int);
+ BEGIN;
+ DROP TABLE test_release1;
+ SAVEPOINT sp1;
+ DROP TABLE test_release2;
+ RELEASE SAVEPOINT sp1;
+ COMMIT;
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_release1')), 1, 'Test 18: release1 logged');
+is(count_drop_logs(drop_table_pattern('public', 'test_release2')), 1, 'Test 18: release2 logged');
+
+# Test 19: Nested SAVEPOINTs
+start_new_test('Test 19: Nested SAVEPOINTs');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_nested1 (id int);
+ CREATE TABLE test_nested2 (id int);
+ CREATE TABLE test_nested3 (id int);
+ BEGIN;
+ SAVEPOINT sp1;
+ DROP TABLE test_nested1;
+ SAVEPOINT sp2;
+ DROP TABLE test_nested2;
+ ROLLBACK TO sp2;
+ SAVEPOINT sp3;
+ DROP TABLE test_nested3;
+ RELEASE SAVEPOINT sp3;
+ RELEASE SAVEPOINT sp1;
+ COMMIT;
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_nested1')), 1, 'Test 19: nested1 logged');
+is(count_drop_logs(drop_table_pattern('public', 'test_nested2')), 0, 'Test 19: nested2 rolled back');
+is(count_drop_logs(drop_table_pattern('public', 'test_nested3')), 1, 'Test 19: nested3 logged');
+$node->safe_psql('postgres', 'DROP TABLE IF EXISTS test_nested2;');
+
+# Test 20: Multiple SAVEPOINTs with partial rollbacks
+start_new_test('Test 20: Multiple SAVEPOINTs');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_msp1 (id int);
+ CREATE TABLE test_msp2 (id int);
+ CREATE TABLE test_msp3 (id int);
+ CREATE TABLE test_msp4 (id int);
+ BEGIN;
+ SAVEPOINT sp_a;
+ DROP TABLE test_msp1;
+ SAVEPOINT sp_b;
+ DROP TABLE test_msp2;
+ SAVEPOINT sp_c;
+ DROP TABLE test_msp3;
+ ROLLBACK TO sp_b;
+ DROP TABLE test_msp4;
+ COMMIT;
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_msp1')), 1, 'Test 20: msp1 logged');
+is(count_drop_logs(drop_table_pattern('public', 'test_msp2')), 0, 'Test 20: msp2 rolled back');
+is(count_drop_logs(drop_table_pattern('public', 'test_msp3')), 0, 'Test 20: msp3 rolled back');
+is(count_drop_logs(drop_table_pattern('public', 'test_msp4')), 1, 'Test 20: msp4 logged');
+$node->safe_psql('postgres', 'DROP TABLE IF EXISTS test_msp2, test_msp3;');
+
+# Test 21: Table dropped in failed transaction block
+start_new_test('Test 21: Transaction block error suppresses drop log');
+$node->safe_psql('postgres', 'CREATE TABLE test_err (id int);');
+$node->psql('postgres', q{
+ BEGIN;
+ DROP TABLE test_err;
+ SELECT 1/0;
+ COMMIT;
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_err')), 0, 'Test 21: Transaction block error suppresses drop log');
+$node->safe_psql('postgres', 'DROP TABLE IF EXISTS test_err;');
+
+# Test 22: Two-Phase Commit (2PC) DROP warning
+start_new_test('Test 22: 2PC DROP emits prepared transaction warning');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_2pc_drop (id int);
+ BEGIN;
+ DROP TABLE test_2pc_drop;
+ PREPARE TRANSACTION 'tx_2pc_drop';
+ COMMIT PREPARED 'tx_2pc_drop';
+});
+my @prep_drop_lines = get_log_lines(qr/table "public\.test_2pc_drop" .* dropped inside a prepared transaction/);
+is(scalar @prep_drop_lines, 1, 'Test 22: 2PC DROP warning logged');
+
+# Test 23: Two-Phase Commit (2PC) TRUNCATE warning
+start_new_test('Test 23: 2PC TRUNCATE emits prepared transaction warning');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_2pc_trunc (id int);
+ BEGIN;
+ TRUNCATE TABLE test_2pc_trunc;
+ PREPARE TRANSACTION 'tx_2pc_trunc';
+ COMMIT PREPARED 'tx_2pc_trunc';
+});
+my @prep_trunc_lines = get_log_lines(qr/table "public\.test_2pc_trunc" .* truncated inside a prepared transaction/);
+is(scalar @prep_trunc_lines, 1, 'Test 23: 2PC TRUNCATE warning logged');
+$node->safe_psql('postgres', 'DROP TABLE test_2pc_trunc;');
+
+# Test 24: DROP DATABASE logged with intermediate LSN
+test_drop_logged(
+ 'Test 24: DROP DATABASE',
+ q{
+ CREATE DATABASE test_drop_db;
+ DROP DATABASE test_drop_db;
+ },
+ drop_database_pattern('test_drop_db')
+);
+
+# Test 25: DROP DATABASE nonexistent with IF EXISTS
+test_drop_not_logged(
+ 'Test 25: DROP DATABASE IF EXISTS nonexistent',
+ q{
+ DROP DATABASE IF EXISTS non_existent_database_12345;
+ },
+ drop_database_pattern('non_existent_database_12345')
+);
+
+# Test 26: Simple TRUNCATE TABLE
+test_drop_logged(
+ 'Test 26: Simple TRUNCATE TABLE',
+ q{
+ CREATE TABLE test_trunc_simple (id int);
+ INSERT INTO test_trunc_simple VALUES (1);
+ TRUNCATE test_trunc_simple;
+ },
+ truncate_table_pattern('public', 'test_trunc_simple')
+);
+
+# Test 27: TRUNCATE TABLE in transaction
+test_drop_logged(
+ 'Test 27: TRUNCATE in transaction',
+ q{
+ CREATE TABLE test_trunc_xact (id int);
+ INSERT INTO test_trunc_xact VALUES (1);
+ BEGIN;
+ TRUNCATE test_trunc_xact;
+ COMMIT;
+ },
+ truncate_table_pattern('public', 'test_trunc_xact')
+);
+
+# Test 28: Rolled back TRUNCATE not logged
+test_drop_not_logged(
+ 'Test 28: Rolled back TRUNCATE',
+ q{
+ CREATE TABLE test_trunc_rollback (id int);
+ INSERT INTO test_trunc_rollback VALUES (1);
+ BEGIN;
+ TRUNCATE test_trunc_rollback;
+ ROLLBACK;
+ },
+ truncate_table_pattern('public', 'test_trunc_rollback')
+);
+
+# Test 29: Multiple tables in single TRUNCATE statement
+test_multiple_truncates(
+ 'Test 29: Multiple tables in single TRUNCATE statement',
+ q{
+ CREATE TABLE test_trunc_multi1 (id int);
+ CREATE TABLE test_trunc_multi2 (id int);
+ CREATE TABLE test_trunc_multi3 (id int);
+ BEGIN;
+ TRUNCATE test_trunc_multi1, test_trunc_multi2, test_trunc_multi3;
+ COMMIT;
+ },
+ ['public', 'test_trunc_multi1', 1],
+ ['public', 'test_trunc_multi2', 1],
+ ['public', 'test_trunc_multi3', 1]
+);
+
+# Test 30: TRUNCATE PARTITIONED TABLE - all tables logged
+test_multiple_truncates(
+ 'Test 30: TRUNCATE Partitioned table',
+ q{
+ CREATE TABLE test_trunc_part (id int, created_at date) PARTITION BY RANGE (created_at);
+ CREATE TABLE test_trunc_part_2024 PARTITION OF test_trunc_part
+ FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
+ CREATE TABLE test_trunc_part_2025 PARTITION OF test_trunc_part
+ FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
+ BEGIN;
+ TRUNCATE test_trunc_part;
+ COMMIT;
+ },
+ ['public', 'test_trunc_part', 1],
+ ['public', 'test_trunc_part_2024', 1],
+ ['public', 'test_trunc_part_2025', 1]
+);
+
+# Test 31: Explicit TRUNCATE of child partition directly is logged
+test_drop_logged(
+ 'Test 31: Direct TRUNCATE of child partition',
+ q{
+ TRUNCATE TABLE test_trunc_part_2024;
+ },
+ truncate_table_pattern('public', 'test_trunc_part_2024')
+);
+$node->safe_psql('postgres', 'DROP TABLE test_trunc_part;');
+
+# Test 32: TRUNCATE CASCADE - cascaded referencing table is logged
+test_multiple_truncates(
+ 'Test 32: TRUNCATE CASCADE with FK references',
+ q{
+ CREATE TABLE test_trunc_parent (id int PRIMARY KEY);
+ CREATE TABLE test_trunc_child (id int, p_id int REFERENCES test_trunc_parent(id));
+ BEGIN;
+ TRUNCATE test_trunc_parent CASCADE;
+ COMMIT;
+ },
+ ['public', 'test_trunc_parent', 1],
+ ['public', 'test_trunc_child', 1]
+);
+$node->safe_psql('postgres', 'DROP TABLE test_trunc_child, test_trunc_parent;');
+
+# Test 33: VACUUM FULL and CLUSTER (should NOT produce false DROP TABLE logs)
+test_drop_not_logged(
+ 'Test 33: VACUUM FULL and CLUSTER do not produce false DROP TABLE log',
+ q{
+ CREATE TABLE test_vac_full (id int);
+ CREATE INDEX test_vac_full_idx ON test_vac_full(id);
+ INSERT INTO test_vac_full VALUES (1);
+ VACUUM FULL test_vac_full;
+ CLUSTER test_vac_full USING test_vac_full_idx;
+ DROP TABLE test_vac_full;
+ },
+ qr/table ".*pg_temp.*" \(OID \d+\) dropped/
+);
+
+done_testing();
--
2.43.0
