Hi, On Wed, Oct 21, 2026 at 4:50 PM Jim Jones <[email protected]> wrote: > > I agree that leaving drop database for another patch is a good idea. > Having it would be quite nice, bit it's indeed much harder than we > anticipated, so I guess it's ok to postpone it. >
Attached is v11, which removes DROP DATABASE as discussed, applies the error style guide to the log messages, and simplifies the TAP test. Best, Salma Elsayed
From 6fb63416e6e63edba33c86a9732b123076060dad Mon Sep 17 00:00:00 2001 From: Salma Elsayed <[email protected]> Date: Mon, 28 Sep 2026 16:24:22 +0300 Subject: [PATCH v11] Add LSN logging for DROP TABLE and TRUNCATE TABLE operations --- doc/src/sgml/config.sgml | 64 +++ src/backend/access/transam/xact.c | 3 + src/backend/access/transam/xlog.c | 1 + src/backend/catalog/dependency.c | 27 ++ 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 | 381 ++++++++++++++++++ src/tools/pgindent/typedefs.list | 1 + 14 files changed, 704 insertions(+) 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 f7db2995c3e..25cf28dd5e0 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8504,6 +8504,70 @@ 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 target recovery LSN (Log Sequence + Number). This records the start LSN of the commit WAL record + (<literal>ProcLastRecPtr</literal>). + </para> + + <para> + Each log entry includes the object name, OID, and the target LSN. For example: + <programlisting> + LOG: table "public.employees" (OID 16384) dropped at LSN 0/015D4A48 + LOG: table "public.employees" (OID 16384) truncated at LSN 0/015D4A80 + </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>). 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 0121a0c8fa0..f5fa881f74c 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 8636ad9ab2c..37225ec3bad 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/tablecmds.c b/src/backend/commands/tablecmds.c index 97e16f3eb47..555e203f1b4 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 at 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 at 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..4b3fa9f4385 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 and TRUNCATE TABLE operations.', + long_desc => 'When enabled, the system will log the LSN (Log Sequence Number) whenever a DROP TABLE or TRUNCATE TABLE 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..4753f4c9826 --- /dev/null +++ b/src/test/recovery/t/061_drop_table_logging.pl @@ -0,0 +1,381 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test log_object_drops: committed DROP TABLE and TRUNCATE TABLE are logged +# with the start LSN of the commit record, and point-in-time recovery to that +# LSN brings the data back. + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('primary'); +$node->init(has_archiving => 1, allows_streaming => 1); +$node->append_conf( + 'postgresql.conf', q{ +log_object_drops = on +max_prepared_transactions = 5 +}); +$node->start; + +my $lsn_re = qr/[0-9A-F]+\/[0-9A-F]+/; +my $any_logged_re = qr/\(OID \d+\) (?:dropped|truncated) at LSN /; +my $hint_re = qr/HINT:\s+To recover dropped or truncated tables/; + +# Run SQL in a single session and return what it added to the server log. +sub log_for +{ + my ($sql) = @_; + my $offset = -s $node->logfile; + + $node->safe_psql('postgres', $sql); + return slurp_file($node->logfile, $offset); +} + +sub count_matches +{ + my ($log, $re) = @_; + my @matches = $log =~ /$re/g; + + return scalar @matches; +} + +sub dropped_re +{ + my ($rel) = @_; + return qr/table "\Q$rel\E" \(OID \d+\) dropped at LSN $lsn_re/; +} + +sub truncated_re +{ + my ($rel) = @_; + return qr/table "\Q$rel\E" \(OID \d+\) truncated at LSN $lsn_re/; +} + +# Recover a new node from the base backup up to (but not including) the +# given LSN. The WAL containing that LSN must already be archived. +sub recover_to +{ + my ($name, $target_lsn) = @_; + + my $pitr = PostgreSQL::Test::Cluster->new($name); + $pitr->init_from_backup($node, 'bkp', has_restoring => 1, standby => 0); + $pitr->append_conf( + 'postgresql.conf', qq{ +recovery_target_lsn = '$target_lsn' +recovery_target_inclusive = false +recovery_target_action = 'promote' +}); + $pitr->start; + $pitr->poll_query_until('postgres', 'SELECT NOT pg_is_in_recovery()') + or die "timed out waiting for $name to be promoted"; + + return $pitr; +} + +my $log; + +# Point-in-time recovery to the logged LSN restores the data. + +$node->backup('bkp'); + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE pitr_drop (id int, val text); + INSERT INTO pitr_drop VALUES (1, 'alpha'), (2, 'beta'); + CREATE TABLE pitr_trunc (id int, val text); + INSERT INTO pitr_trunc VALUES (1, 'foo'), (2, 'bar'), (3, 'baz'); +}); + +$log = log_for('DROP TABLE pitr_drop'); +my ($drop_lsn) = + $log =~ /table "public\.pitr_drop" \(OID \d+\) dropped at LSN ($lsn_re)/; +ok(defined $drop_lsn, 'DROP TABLE is logged with an LSN') + or die "cannot continue without the DROP TABLE LSN"; + +$log = log_for('TRUNCATE pitr_trunc'); +my ($trunc_lsn) = + $log =~ /table "public\.pitr_trunc" \(OID \d+\) truncated at LSN ($lsn_re)/; +ok(defined $trunc_lsn, 'TRUNCATE is logged with an LSN') + or die "cannot continue without the TRUNCATE LSN"; + +my $walfile = + $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' <= last_archived_wal FROM pg_stat_archiver") + or die "timed out waiting for WAL archiving"; + +my $pitr = recover_to('pitr_drop', $drop_lsn); +is( $pitr->safe_psql( + 'postgres', "SELECT string_agg(val, ',' ORDER BY id) FROM pitr_drop"), + 'alpha,beta', + 'recovery to the logged LSN restores the dropped table'); +$pitr->teardown_node; + +$pitr = recover_to('pitr_trunc', $trunc_lsn); +is( $pitr->safe_psql( + 'postgres', "SELECT string_agg(val, ',' ORDER BY id) FROM pitr_trunc"), + 'foo,bar,baz', + 'recovery to the logged LSN restores the truncated rows'); +$pitr->teardown_node; + +# A committed DROP TABLE logs the LSN and a recovery hint. + +$node->safe_psql('postgres', 'CREATE TABLE t_simple (id int)'); +$log = log_for('DROP TABLE t_simple'); +is(count_matches($log, dropped_re('public.t_simple')), + 1, 'committed DROP TABLE is logged'); +is(count_matches($log, $hint_re), 1, 'recovery hint is logged'); +unlike($log, qr/at LSN 0\/0+\b/, 'logged LSN is valid'); + +# Rolled back DROP and TRUNCATE are not logged. + +$node->safe_psql('postgres', 'CREATE TABLE t_rollback (id int)'); +$log = log_for( + q{ + BEGIN; + DROP TABLE t_rollback; + ROLLBACK; + BEGIN; + TRUNCATE t_rollback; + ROLLBACK; +}); +is(count_matches($log, $any_logged_re), + 0, 'rolled back DROP TABLE and TRUNCATE are not logged'); +$node->safe_psql('postgres', 'DROP TABLE t_rollback'); + +# Several tables in one transaction share one LSN and get one hint. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE t_multi1 (id int); + CREATE TABLE t_multi2 (id int); + CREATE TABLE t_multi3 (id int); +}); +$log = log_for( + q{ + BEGIN; + DROP TABLE t_multi1, t_multi2; + TRUNCATE t_multi3; + COMMIT; +}); +is(count_matches($log, dropped_re('public.t_multi1')), + 1, 'first table of a multi-table DROP is logged'); +is(count_matches($log, dropped_re('public.t_multi2')), + 1, 'second table of a multi-table DROP is logged'); +is(count_matches($log, truncated_re('public.t_multi3')), + 1, 'TRUNCATE in the same transaction is logged'); +my %lsns = map { $_ => 1 } $log =~ /$any_logged_re($lsn_re)/g; +is(scalar keys %lsns, 1, 'all tables in one transaction share the same LSN'); +is(count_matches($log, $hint_re), 1, 'one recovery hint per transaction'); +$node->safe_psql('postgres', 'DROP TABLE t_multi3'); + +# DROP SCHEMA CASCADE logs every table in the schema, including a partition +# whose parent lives in another schema, but not that parent. + +$log = log_for( + q{ + CREATE SCHEMA s_parent; + CREATE SCHEMA s_drop; + CREATE TABLE s_drop.t1 (id int); + CREATE TABLE s_parent.p (id int) PARTITION BY RANGE (id); + CREATE TABLE s_drop.p1 PARTITION OF s_parent.p FOR VALUES FROM (1) TO (10); + DROP SCHEMA s_drop CASCADE; +}); +is(count_matches($log, dropped_re('s_drop.t1')), + 1, 'DROP SCHEMA CASCADE logs a table in the schema'); +is(count_matches($log, dropped_re('s_drop.p1')), + 1, 'DROP SCHEMA CASCADE logs a partition in the schema'); +is(count_matches($log, dropped_re('s_parent.p')), + 0, 'DROP SCHEMA CASCADE does not log the parent in another schema'); +$node->safe_psql('postgres', 'DROP SCHEMA s_parent CASCADE'); + +# Partitioned tables: the parent and every partition are logged. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE t_part (id int) PARTITION BY RANGE (id); + CREATE TABLE t_part1 PARTITION OF t_part FOR VALUES FROM (1) TO (10); + CREATE TABLE t_part2 PARTITION OF t_part FOR VALUES FROM (10) TO (20); +}); +$log = log_for('TRUNCATE t_part'); +is(count_matches($log, truncated_re("public.$_")), + 1, "TRUNCATE of a partitioned table logs $_") + foreach qw(t_part t_part1 t_part2); +$log = log_for('DROP TABLE t_part'); +is(count_matches($log, dropped_re("public.$_")), + 1, "DROP of a partitioned table logs $_") + foreach qw(t_part t_part1 t_part2); + +# Foreign keys: TRUNCATE CASCADE truncates and logs the referencing table, +# while DROP TABLE CASCADE only drops the constraint, so only the dropped +# table is logged. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE t_fk_parent (id int PRIMARY KEY); + CREATE TABLE t_fk_child (parent_id int REFERENCES t_fk_parent); +}); +$log = log_for('TRUNCATE t_fk_parent CASCADE'); +is(count_matches($log, truncated_re('public.t_fk_child')), + 1, 'TRUNCATE CASCADE logs the referencing table'); +$log = log_for('DROP TABLE t_fk_parent CASCADE'); +is(count_matches($log, dropped_re('public.t_fk_parent')), + 1, 'DROP TABLE CASCADE logs the dropped table'); +is(count_matches($log, dropped_re('public.t_fk_child')), + 0, 'DROP TABLE CASCADE does not log a table that only lost a constraint'); +$node->safe_psql('postgres', 'DROP TABLE t_fk_child'); + +# Savepoints: work inside a rolled back savepoint is not logged, including +# work from a nested savepoint that was released into it first. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE sp_a (id int); + CREATE TABLE sp_b (id int); + CREATE TABLE sp_c (id int); + CREATE TABLE sp_d (id int); + CREATE TABLE sp_e (id int); +}); +$log = log_for( + q{ + BEGIN; + DROP TABLE sp_a; + SAVEPOINT s1; + DROP TABLE sp_b; + SAVEPOINT s2; + DROP TABLE sp_c; + RELEASE SAVEPOINT s2; + ROLLBACK TO SAVEPOINT s1; + DROP TABLE sp_d; + SAVEPOINT s3; + TRUNCATE sp_e; + RELEASE SAVEPOINT s3; + COMMIT; +}); +is(count_matches($log, dropped_re('public.sp_a')), + 1, 'drop before any savepoint is logged'); +is(count_matches($log, dropped_re('public.sp_b')), + 0, 'drop in a rolled back savepoint is not logged'); +is(count_matches($log, dropped_re('public.sp_c')), + 0, 'drop released into a rolled back savepoint is not logged'); +is(count_matches($log, dropped_re('public.sp_d')), + 1, 'drop after ROLLBACK TO is logged'); +is(count_matches($log, truncated_re('public.sp_e')), + 1, 'truncate in a released savepoint is logged'); +$node->safe_psql('postgres', 'DROP TABLE sp_b, sp_c, sp_e'); + +# A PL/pgSQL exception block rolls back its internal subtransaction. + +$node->safe_psql('postgres', 'CREATE TABLE t_exception (id int)'); +$log = log_for( + q{ + DO $$ + BEGIN + DROP TABLE t_exception; + RAISE EXCEPTION 'undo the drop'; + EXCEPTION WHEN OTHERS THEN + NULL; + END + $$; +}); +is(count_matches($log, dropped_re('public.t_exception')), + 0, 'drop undone by an exception block is not logged'); +$node->safe_psql('postgres', 'DROP TABLE t_exception'); + +# Temporary and unlogged tables, and relations that are not tables, are not +# logged. The pattern matches any schema, so this also covers the pg_temp_N +# schema of the temporary table. + +$log = log_for( + q{ + CREATE TEMP TABLE t_temp (id int); + TRUNCATE t_temp; + DROP TABLE t_temp; + CREATE UNLOGGED TABLE t_unlogged (id int); + TRUNCATE t_unlogged; + DROP TABLE t_unlogged; + CREATE TABLE t_base (id int); + CREATE VIEW t_view AS SELECT * FROM t_base; + CREATE MATERIALIZED VIEW t_matview AS SELECT * FROM t_base; + CREATE INDEX t_base_idx ON t_base (id); + CREATE SEQUENCE t_seq; + DROP VIEW t_view; + DROP MATERIALIZED VIEW t_matview; + DROP INDEX t_base_idx; + DROP SEQUENCE t_seq; + DROP TABLE IF EXISTS t_does_not_exist; +}); +is(count_matches($log, $any_logged_re), + 0, 'temporary, unlogged and non-table relations are not logged'); +$node->safe_psql('postgres', 'DROP TABLE t_base'); + +# Commands that rewrite a table drop a transient heap internally, which must +# not be logged. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE t_rewrite (id int); + CREATE INDEX t_rewrite_idx ON t_rewrite (id); + INSERT INTO t_rewrite VALUES (1); + CREATE MATERIALIZED VIEW t_rewrite_mv AS SELECT 1 AS a; +}); +$log = log_for( + q{ + ALTER TABLE t_rewrite ALTER COLUMN id TYPE bigint; + VACUUM FULL t_rewrite; + CLUSTER t_rewrite USING t_rewrite_idx; + REFRESH MATERIALIZED VIEW t_rewrite_mv; +}); +is(count_matches($log, $any_logged_re), + 0, 'table rewrites do not log a drop'); +$node->safe_psql('postgres', + 'DROP TABLE t_rewrite; DROP MATERIALIZED VIEW t_rewrite_mv;'); + +# Nothing is logged when the parameter is off. + +$node->safe_psql('postgres', 'CREATE TABLE t_off (id int)'); +$log = log_for( + q{ + SET log_object_drops = off; + TRUNCATE t_off; + DROP TABLE t_off; +}); +is(count_matches($log, $any_logged_re), + 0, 'nothing is logged with log_object_drops = off'); + +# Prepared transactions log a message at PREPARE time but no recovery LSN. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE t_2pc_drop (id int); + CREATE TABLE t_2pc_trunc (id int); +}); +$log = log_for( + q{ + BEGIN; + DROP TABLE t_2pc_drop; + TRUNCATE t_2pc_trunc; + PREPARE TRANSACTION 'p1'; + COMMIT PREPARED 'p1'; +}); +is( count_matches( + $log, + qr/table "public\.t_2pc_drop" \(OID \d+\) is being dropped inside a prepared transaction/ + ), + 1, + 'DROP TABLE in a prepared transaction is reported'); +is( count_matches( + $log, + qr/table "public\.t_2pc_trunc" \(OID \d+\) is being truncated inside a prepared transaction/ + ), + 1, + 'TRUNCATE in a prepared transaction is reported'); +is(count_matches($log, $any_logged_re), + 0, 'no recovery LSN is logged for a prepared transaction'); +$node->safe_psql('postgres', 'DROP TABLE t_2pc_trunc'); + +done_testing(); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index b964e5ee907..27e1ff4758f 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
