Hi Jim,
Thanks for your response.
>
> postgres=# create table t (g int, pad text);
> CREATE TABLE
> postgres=# insert into t select g, repeat('x', 100) from
> generate_series(1, 600000) g;
> INSERT 0 600000
> postgres=# create database db;
> CREATE DATABASE
> postgres=# update t set g = g + 1;
> UPDATE 600000
> postgres=# set log_object_drops to on;
> SET
> postgres=# set statement_timeout = '20ms';
> SET
> postgres=# drop database db;
> ERROR: canceling statement due to statement timeout
>
> postgres=# select oid,datname from pg_database where datname = 'db';
> oid | datname
> -------+---------
> 16414 | db
> (1 row)
>
>
> .. but the logs show:
>
> LOG: database "db" (OID 16414) dropped, lsn=0/47AAC7E0
>
But in this exact scenario, didn't the database become invalid?
postgres=# \c db
connection to server on socket "/tmp/.s.PGSQL.5435" failed: FATAL:
cannot connect to invalid database "db"
HINT: Use DROP DATABASE to drop invalid databases.
The LSN value itself is fine to recover to. The problem is the word
"dropped," since the drop did not actually complete.
I tested with injection points. If an error occurs after v9's logging
point, we get the same situation again: the log assumes the database
was dropped when it was not. I moved the LSN registration earlier in
dropdb(), so that a failure after that point still has something to
log.
I am now using a callback, similar to how we handle DROP TABLE. On
commit, we log the LSN as before. On abort, we still log a message,
but state the drop did not complete instead of saying it dropped. If
"drop database db;" is retried on an already invalid database, we
still log a message, but without a recovery_target_lsn, and we state
that the original recovery point is not known to this session.
Attached is v10 with 0002 for the callback implementation. Let me know
what you think.
It still needs more testing.
Best regards,
Salma Elsayed
From eec9dea6cb95ee224d21bc3a7504de6d653171f2 Mon Sep 17 00:00:00 2001
From: Salma Elsayed <[email protected]>
Date: Thu, 15 Oct 2026 17:05:09 +0300
Subject: [PATCH v10 2/2] Use a callback to log DROP DATABASE
---
src/backend/commands/dbcommands.c | 97 +++++++++++++++++++++++++++----
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 88 insertions(+), 10 deletions(-)
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index fdb8da20a8a..07672e3e248 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -1710,6 +1710,86 @@ createdb_failure_callback(int code, Datum arg)
remove_dbtablespaces(fparms->dest_dboid);
}
+typedef struct PendingDropdbLog
+{
+ Oid db_id;
+ char dbname[NAMEDATALEN];
+ XLogRecPtr drop_lsn;
+ bool already_invalid;
+ bool registered;
+} PendingDropdbLog;
+
+static PendingDropdbLog pending_dropdb_log = {0};
+static bool dropdb_callback_registered = false;
+
+/* Transaction callback that logs the result of a DROP DATABASE */
+static void
+DropDbXactCallback(XactEvent event, void *arg)
+{
+ if (!pending_dropdb_log.registered)
+ return;
+
+ if (event == XACT_EVENT_COMMIT)
+ {
+ if (pending_dropdb_log.already_invalid)
+ ereport(LOG,
+ (errmsg("database \"%s\" (OID %u) dropped",
+ pending_dropdb_log.dbname, pending_dropdb_log.db_id),
+ errdetail("This database was already left invalid by an "
+ "earlier failed attempt before this drop began; "
+ "the original recovery point is not known to "
+ "this session.")));
+ else
+ ereport(LOG,
+ (errmsg("database \"%s\" (OID %u) dropped, lsn=%X/%08X",
+ pending_dropdb_log.dbname, pending_dropdb_log.db_id,
+ LSN_FORMAT_ARGS(pending_dropdb_log.drop_lsn)),
+ errhint("To recover to the point before this drop, use "
+ "recovery_target_lsn = '%X/%08X' with "
+ "recovery_target_inclusive = false.",
+ LSN_FORMAT_ARGS(pending_dropdb_log.drop_lsn))));
+ pending_dropdb_log.registered = false;
+ }
+ else if (event == XACT_EVENT_ABORT)
+ {
+ if (pending_dropdb_log.already_invalid)
+ ereport(WARNING,
+ (errmsg("DROP DATABASE \"%s\" (OID %u) did not complete",
+ pending_dropdb_log.dbname, pending_dropdb_log.db_id),
+ errdetail("This database was already invalid before this "
+ "attempt; the original recovery point is not "
+ "known to this session.")));
+ else
+ ereport(WARNING,
+ (errmsg("DROP DATABASE \"%s\" (OID %u) did not complete; "
+ "the database may be left invalid and require "
+ "DROP DATABASE to be run again",
+ pending_dropdb_log.dbname, pending_dropdb_log.db_id),
+ errhint("Files belonging to this database may already have "
+ "been removed. To recover data from before this "
+ "attempt, use recovery_target_lsn = '%X/%08X' with "
+ "recovery_target_inclusive = false.",
+ LSN_FORMAT_ARGS(pending_dropdb_log.drop_lsn))));
+ pending_dropdb_log.registered = false;
+ }
+}
+
+/* Remember a pending DROP DATABASE for DropDbXactCallback to log */
+static void
+RegisterDropDatabaseLog(Oid db_id, const char *dbname, XLogRecPtr drop_lsn, bool already_invalid)
+{
+ if (!dropdb_callback_registered)
+ {
+ RegisterXactCallback(DropDbXactCallback, NULL);
+ dropdb_callback_registered = true;
+ }
+
+ pending_dropdb_log.db_id = db_id;
+ strlcpy(pending_dropdb_log.dbname, dbname, NAMEDATALEN);
+ pending_dropdb_log.drop_lsn = drop_lsn;
+ pending_dropdb_log.registered = true;
+ pending_dropdb_log.already_invalid = already_invalid;
+}
/*
* DROP DATABASE
@@ -1730,6 +1810,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
nslots_active;
int nsubscriptions;
XLogRecPtr drop_lsn = InvalidXLogRecPtr;
+ bool already_invalid = false;
/*
* Look up the target database's OID, and get exclusive lock on it. We
@@ -1760,6 +1841,9 @@ dropdb(const char *dbname, bool missing_ok, bool force)
}
}
+ if (log_object_drops)
+ already_invalid = database_is_invalid_oid(db_id);
+
/*
* Permission checks
*/
@@ -1869,8 +1953,10 @@ dropdb(const char *dbname, bool missing_ok, bool force)
* invalid marker set below.
*/
if (log_object_drops)
+ {
drop_lsn = GetXLogInsertRecPtr();
-
+ RegisterDropDatabaseLog(db_id, dbname, drop_lsn, already_invalid);
+ }
/*
* Except for the deletion of the catalog row, subsequent actions are not
* transactional (consider DropDatabaseBuffers() discarding modified
@@ -1938,15 +2024,6 @@ dropdb(const char *dbname, bool missing_ok, bool force)
*/
remove_dbtablespaces(db_id);
- /* Log the LSN captured before the database was marked invalid */
- if (log_object_drops)
- ereport(LOG,
- (errmsg("database \"%s\" (OID %u) dropped, lsn=%X/%08X",
- dbname, db_id, LSN_FORMAT_ARGS(drop_lsn)),
- errhint("To recover to the point before this drop, use recovery_target_lsn = '%X/%08X' "
- "with recovery_target_inclusive = false.",
- LSN_FORMAT_ARGS(drop_lsn))));
-
/*
* Close pg_database, but keep lock till commit.
*/
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3fa60d7fd6e..966d8faed98 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1911,6 +1911,7 @@ PATH
PCtxtHandle
PERL_CONTEXT
PERL_SI
+PendingDropdbLog
PFN
PGAlignedBlock
PGAlignedXLogBlock
--
2.43.0
From 7f83812a1da49f253b36ff17764c8ab02566eea3 Mon Sep 17 00:00:00 2001
From: Salma Elsayed <[email protected]>
Date: Mon, 28 Sep 2026 16:24:22 +0300
Subject: [PATCH v10 1/2] Add LSN logging for DROP/TRUNCATE TABLE and DROP
DATABASE operations
---
doc/src/sgml/config.sgml | 69 +-
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 | 21 +
src/backend/commands/tablecmds.c | 212 ++++
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/061_drop_table_logging.pl | 935 ++++++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
15 files changed, 1283 insertions(+), 1 deletion(-)
create mode 100644 src/test/recovery/t/061_drop_table_logging.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e537b3b0043..709608672b4 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,74 @@ 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>, <command>TRUNCATE TABLE</command>,
+ and <command>DROP DATABASE</command> operations to be logged with their target
+ recovery LSN (Log Sequence Number). For table operations, this records the
+ start LSN of the commit WAL record (<literal>ProcLastRecPtr</literal>).
+ For database operations, this records the WAL insert position immediately
+ prior to marking the database invalid and deleting its files.
+ </para>
+
+ <para>
+ Each log entry includes the object name, OID, and the target 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. Prepared
+ transactions (<command>PREPARE TRANSACTION</command>) log a notification
+ at prepare time, but do not record a recovery target LSN because the
+ eventual commit occurs in a separate two-phase commit transaction.
+ </para>
+
+ <para>
+ This parameter is useful for tracking destructive operations and determining
+ a recovery target for point-in-time recovery (<literal>recovery_target_lsn</literal>
+ with <literal>recovery_target_inclusive = false</literal>). Note that
+ recovering to the logged LSN stops replay immediately before the commit
+ record of that transaction. Consequently, all other changes within the same
+ transaction are also omitted from the recovered database. Tables created
+ and dropped within the same transaction cannot be restored via point-in-time
+ recovery because they never existed in a prior committed state.
+ </para>
+
+ <para>
+ Note that enabling this option may generate substantial log volume when
+ dropping or truncating schemas, or partitioned tables, as each table is
+ logged individually.
+ </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 702cbb9df10..0556aa366de 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,
@@ -2190,6 +2192,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 63d214782da..640f0d7f561 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 e0fe2a477b0..b0466851fdc 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 7e3fc59eafd..fdb8da20a8a 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"
@@ -1728,6 +1729,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
int nslots,
nslots_active;
int nsubscriptions;
+ XLogRecPtr drop_lsn = InvalidXLogRecPtr;
/*
* Look up the target database's OID, and get exclusive lock on it. We
@@ -1859,6 +1861,16 @@ dropdb(const char *dbname, bool missing_ok, bool force)
*/
pgstat_drop_database(db_id);
+ /*
+ * Capture the recovery target LSN for log_object_drops before the
+ * non-transactional in-place update below. Everything WAL-logged before
+ * this point is transactional, so recovering up to (but not including)
+ * this LSN yields a fully valid database. Any later LSN would replay the
+ * invalid marker set below.
+ */
+ if (log_object_drops)
+ drop_lsn = GetXLogInsertRecPtr();
+
/*
* Except for the deletion of the catalog row, subsequent actions are not
* transactional (consider DropDatabaseBuffers() discarding modified
@@ -1926,6 +1938,15 @@ dropdb(const char *dbname, bool missing_ok, bool force)
*/
remove_dbtablespaces(db_id);
+ /* Log the LSN captured before the database was marked invalid */
+ if (log_object_drops)
+ ereport(LOG,
+ (errmsg("database \"%s\" (OID %u) dropped, lsn=%X/%08X",
+ dbname, db_id, LSN_FORMAT_ARGS(drop_lsn)),
+ errhint("To recover to the point before this drop, use recovery_target_lsn = '%X/%08X' "
+ "with recovery_target_inclusive = false.",
+ LSN_FORMAT_ARGS(drop_lsn))));
+
/*
* Close pg_database, but keep lock till commit.
*/
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 93ad49e59fd..d906e6df342 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -352,6 +352,200 @@ 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;
+
+ 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)
+ {
+ 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;
+ }
+ }
+ }
+}
+
+/*
+ * 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) is being 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) is being 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
{
@@ -2174,6 +2368,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 380679a76c0..c4378e8ecb3 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -1786,6 +1786,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 342aaeef59a..241c6e51543 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 01f98de8a9f..f02fe45c688 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 2bf5714b51e..6aa7fddcd78 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 c3d8518cb62..5bc66c4e35c 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 164efba6b51..473aed59d8b 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 b6ac9dd07a7..b6a63a524b9 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -69,6 +69,7 @@ tests += {
't/058_shutdown_crash_restart.pl',
't/059_remote_apply_status_interval.pl',
't/060_multixact_crashed_updater.pl',
+ 't/061_drop_table_logging.pl',
],
},
}
diff --git a/src/test/recovery/t/061_drop_table_logging.pl b/src/test/recovery/t/061_drop_table_logging.pl
new file mode 100644
index 00000000000..6f040226f8b
--- /dev/null
+++ b/src/test/recovery/t/061_drop_table_logging.pl
@@ -0,0 +1,935 @@
+# 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, lsn=/;
+}
+
+# Build pattern for TRUNCATE TABLE log entry
+sub truncate_table_pattern
+{
+ my ($schema, $table) = @_;
+ return qr/table "$schema\.$table" \(OID \d+\) truncated, lsn=/;
+}
+
+# Build pattern for DROP DATABASE log entry
+sub drop_database_pattern
+{
+ my ($dbname) = @_;
+ return qr/database "$dbname" \(OID \d+\) dropped, lsn=/;
+}
+
+# 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
+# ==============================================================================
+
+# Note on LSN semantics: With recovery_target_inclusive = false, recovery stops
+# before the first record whose start LSN is >= recovery_target_lsn. Therefore,
+# recovery only preserves the dropped object if the logged LSN is the START of the
+# commit/drop record (ProcLastRecPtr), not its end.
+sub run_pitr_test
+{
+ my ($test_node_name, $target_lsn, $verify_sub) = @_;
+
+ BAIL_OUT("Target LSN is undef for $test_node_name") unless defined $target_lsn;
+
+ # Restore and promote a PITR node from base backup (standby => 0)
+ my $pitr_node = PostgreSQL::Test::Cluster->new($test_node_name);
+ $pitr_node->init_from_backup($node, 'bkp', has_restoring => 1, standby => 0);
+ $pitr_node->append_conf('postgresql.conf', qq{
+recovery_target_lsn = '$target_lsn'
+recovery_target_inclusive = false
+recovery_target_action = 'promote'
+});
+ $pitr_node->start;
+ $pitr_node->poll_query_until('postgres', "SELECT pg_is_in_recovery() = 'f';")
+ or BAIL_OUT("Timed out waiting for PITR promotion for $test_node_name");
+
+ $verify_sub->($pitr_node);
+ $pitr_node->teardown_node;
+}
+
+# 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 = @drop_lines ? extract_lsn($drop_lines[0]) : undef;
+ok(defined $drop_lsn, 'PITR: Scraped drop commit LSN is defined');
+
+# 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";
+
+run_pitr_test('pitr_drop', $drop_lsn, sub {
+ my ($n) = @_;
+ my $drop_count = $n->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 = $n->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');
+});
+
+
+# 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 = @trunc_lines ? extract_lsn($trunc_lines[0]) : undef;
+ok(defined $trunc_lsn, 'PITR: Scraped truncate commit LSN is defined');
+
+# 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";
+
+run_pitr_test('pitr_trunc', $trunc_lsn, sub {
+ my ($n) = @_;
+ my $trunc_count = $n->safe_psql('postgres',
+ "SELECT count(*) FROM pitr_trunc_table;");
+ is($trunc_count, '3', 'PITR TRUNCATE: All 3 rows preserved');
+ my $trunc_vals = $n->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');
+});
+
+
+# PITR Test 3: DROP DATABASE recovery
+start_new_test('PITR recovery for DROP DATABASE');
+$node->safe_psql('postgres', q{
+ CREATE DATABASE pitr_drop_db WITH CONNECTION LIMIT 42;
+});
+$node->safe_psql('pitr_drop_db', q{
+ CREATE TABLE pitr_db_data (id int, val text);
+ INSERT INTO pitr_db_data VALUES (1, 'hello'), (2, 'world');
+});
+$node->safe_psql('postgres', q{
+ DROP DATABASE pitr_drop_db;
+});
+my @db_drop_lines = get_log_lines(drop_database_pattern('pitr_drop_db'));
+is(scalar @db_drop_lines, 1, 'PITR: DROP DATABASE logged');
+my $db_drop_lsn = @db_drop_lines ? extract_lsn($db_drop_lines[0]) : undef;
+ok(defined $db_drop_lsn, 'PITR: Scraped drop database LSN is defined');
+
+# Switch WAL to make sure the record is archived
+my $walfile_db_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_db_drop' <= last_archived_wal FROM pg_stat_archiver;")
+ or die "Timed out waiting for WAL archival";
+
+run_pitr_test('pitr_drop_db', $db_drop_lsn, sub {
+ my ($n) = @_;
+ my $db_count = $n->safe_psql('pitr_drop_db',
+ "SELECT count(*) FROM pitr_db_data;");
+ is($db_count, '2', 'PITR DROP DB: Database exists with all 2 rows restored');
+ my $db_vals = $n->safe_psql('pitr_drop_db',
+ "SELECT string_agg(val, ',' ORDER BY id) FROM pitr_db_data;");
+ is($db_vals, 'hello,world', 'PITR DROP DB: Table row contents match exactly');
+
+ # Verify that original connection limit was preserved without manual intervention
+ my $db_connlimit = $n->safe_psql('postgres',
+ "SELECT datconnlimit FROM pg_database WHERE datname = 'pitr_drop_db';");
+ is($db_connlimit, '42', 'PITR DROP DB: Original connection limit preserved');
+});
+
+
+# ==============================================================================
+# 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_multiple_drops(
+ 'Test 5: DROP TABLE CASCADE with foreign keys - only explicitly dropped table is logged',
+ 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;
+ },
+ ['public', 'test_parent', 1],
+ ['public', 'test_child', 0]
+);
+$node->safe_psql('postgres', 'DROP TABLE IF EXISTS test_child;');
+
+# 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
+# Note on same-transaction create-and-drop: Tables created and dropped within
+# the same transaction are logged upon commit because the drop is registered
+# during dependency deletion. However, point-in-time recovery cannot restore them
+# because they never existed in a prior committed state.
+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/".*test_view" \(OID \d+\) dropped/
+);
+
+# Test 12b: DROP TABLE IF EXISTS on non-existent table (should NOT be logged)
+test_drop_not_logged(
+ 'Test 12b: DROP TABLE IF EXISTS on non-existent table',
+ 'DROP TABLE IF EXISTS non_existent_table_9999;',
+ qr/"non_existent_table_9999" \(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/".*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/".*test_idx" \(OID \d+\) dropped/
+);
+
+# Test 14b: DROP SEQUENCE (should NOT be logged)
+test_drop_not_logged(
+ 'Test 14b: SEQUENCE drop not logged',
+ q{
+ CREATE SEQUENCE test_seq;
+ DROP SEQUENCE test_seq;
+ },
+ qr/".*test_seq" \(OID \d+\) dropped/
+);
+
+# Test 15: Table rewrite (ALTER TABLE ... ALTER COLUMN TYPE) does not emit drop logs
+start_new_test('Test 15: Table rewrite does not emit drop logs');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_rewrite (id int);
+ INSERT INTO test_rewrite VALUES (1);
+ ALTER TABLE test_rewrite ALTER COLUMN id TYPE bigint;
+});
+my @rewrite_drops = get_log_lines(qr/\(OID \d+\) dropped/);
+is(scalar @rewrite_drops, 0, 'Test 15: ALTER TABLE TYPE emitted 0 drop logs');
+$node->safe_psql('postgres', 'DROP TABLE test_rewrite;');
+
+# 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" .* is being dropped inside a prepared transaction/);
+is(scalar @prep_drop_lines, 1, 'Test 22: 2PC DROP warning logged');
+
+# Test 22b: Two-Phase Commit (2PC) ROLLBACK PREPARED (should NOT be logged)
+start_new_test('Test 22b: 2PC ROLLBACK PREPARED not logged');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_2pc_rb (id int);
+ BEGIN;
+ DROP TABLE test_2pc_rb;
+ PREPARE TRANSACTION 'tx_2pc_rb';
+ ROLLBACK PREPARED 'tx_2pc_rb';
+});
+is(count_drop_logs(drop_table_pattern('public', 'test_2pc_rb')), 0, 'Test 22b: 2PC ROLLBACK PREPARED not logged');
+$node->safe_psql('postgres', 'DROP TABLE test_2pc_rb;');
+
+# 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" .* is being 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 target 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'),
+ sub {
+ my ($line) = @_;
+ my @hints = get_log_lines(qr/HINT:\s+To recover to the point before this drop/);
+ is(scalar @hints, 1, 'Test 24: HINT emitted for database drop');
+ }
+);
+
+# Test 24b: DROP DATABASE WITH (FORCE) is logged
+test_drop_logged(
+ 'Test 24b: DROP DATABASE WITH (FORCE)',
+ q{
+ CREATE DATABASE test_drop_force;
+ DROP DATABASE test_drop_force WITH (FORCE);
+ },
+ drop_database_pattern('test_drop_force')
+);
+
+# 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')
+);
+$node->safe_psql('postgres', 'DROP TABLE test_trunc_rollback;');
+
+# Test 28b: TRUNCATE temporary table (should NOT be logged)
+test_drop_not_logged(
+ 'Test 28b: TRUNCATE temporary table not logged',
+ q{
+ CREATE TEMP TABLE test_trunc_temp (id int);
+ TRUNCATE test_trunc_temp;
+ },
+ truncate_table_pattern('pg_temp', 'test_trunc_temp')
+);
+
+# Test 28c: TRUNCATE UNLOGGED table (should NOT be logged)
+test_drop_not_logged(
+ 'Test 28c: TRUNCATE UNLOGGED table not logged',
+ q{
+ CREATE UNLOGGED TABLE test_trunc_unlogged (id int);
+ TRUNCATE test_trunc_unlogged;
+ DROP TABLE test_trunc_unlogged;
+ },
+ truncate_table_pattern('public', 'test_trunc_unlogged')
+);
+
+# Test 28d: TRUNCATE in rolled-back savepoint (should NOT be logged)
+start_new_test('Test 28d: TRUNCATE in rolled-back savepoint not logged');
+$node->safe_psql('postgres', q{
+ CREATE TABLE test_trunc_sp (id int);
+ BEGIN;
+ SAVEPOINT sp1;
+ TRUNCATE test_trunc_sp;
+ ROLLBACK TO sp1;
+ COMMIT;
+});
+is(count_drop_logs(truncate_table_pattern('public', 'test_trunc_sp')), 0, 'Test 28d: TRUNCATE in rolled-back savepoint not logged');
+$node->safe_psql('postgres', 'DROP TABLE test_trunc_sp;');
+
+# 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)
+start_new_test('Test 33: VACUUM FULL and CLUSTER do not produce false DROP TABLE logs');
+$node->safe_psql('postgres', 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;
+});
+my @vac_drops = get_log_lines(qr/\(OID \d+\) dropped/);
+is(scalar @vac_drops, 0, 'Test 33: VACUUM FULL and CLUSTER emitted 0 drop logs');
+$node->safe_psql('postgres', 'DROP TABLE test_vac_full;');
+
+done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a7a8dbdd23f..3fa60d7fd6e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -697,6 +697,7 @@ DomainConstraintState
DomainConstraintType
DomainIOData
DropBehavior
+DropOrTruncateTableInfo
DropOwnedStmt
DropReplicationSlotCmd
DropRoleStmt
--
2.43.0
