Hi Jim, Thanks for the review.
On Thu, Oct 29, 2026 at 6:24 PM Jim Jones <[email protected]> wrote: > == callback functions == > > The comments of RegisterXactCallback and RegisterSubXactCallback suggest > they're meant for extensions: "These functions are intended for use by > dynamically loaded modules.". Are they really the best fit here? > I removed the RegisterXactCallback and RegisterSubXactCallback calls and replaced them with AtEOSubXact_DropOrTruncateTable and AtEOXact_DropOrTruncateTable. They are now called directly from xact.c, next to AtEOXact_on_commit_actions and AtEOSubXact_on_commit_actions, which already handle ON COMMIT actions this way. > == RegisterDropOrTruncateTable == > > The function already expects reloid, so it could look for rel_name and > rel_namespace itself if needed -- currently the caller needs to do it. > Have you considered simply (relid, is_truncate)? > Done. It now takes (relid, is_truncate) and looks up the names itself. > == doDeletion == > > Is the relname != NULL guard and the "unknown" schemaname really > necessary? I imagined that at this point the relation is locked and must > exist. Also, IMHO silently skipping a log entry or just logging a > placeholder is arguably not a good idea for this feature. > Agreed. I removed the relname check and the "unknown" schema name. > == prepared transactions == > > A simplistic approach would be just to make it explicit in the docs and > not log anything. > Agreed. Nothing is logged for prepared transactions now, and the docs say so. While testing I also found that TRUNCATE of a partitioned table with no partitions hit an Assert when wal_level is minimal, because no commit record is written. Fixed in this version. v12 attached. Best, Salma Elsayed
From a9272a93cb0d74564936b21d061da14d66563e11 Mon Sep 17 00:00:00 2001 From: Salma Elsayed <[email protected]> Date: Fri, 30 Oct 2026 15:22:10 +0200 Subject: [PATCH v12] Log the recovery LSN of dropped and truncated tables When the new log_object_drops parameter is enabled, committed DROP TABLE and TRUNCATE TABLE operations are logged together with the start LSN of the transaction's commit record. Using that LSN as recovery_target_lsn with recovery_target_inclusive = false recovers the data as it was before the operation. Only permanent tables and partitioned tables are logged. Operations that are rolled back, including those undone by ROLLBACK TO SAVEPOINT, are not logged, and neither are operations in prepared transactions. --- doc/src/sgml/config.sgml | 61 +++ src/backend/access/transam/xact.c | 10 + src/backend/access/transam/xlog.c | 1 + src/backend/catalog/dependency.c | 10 + src/backend/commands/tablecmds.c | 151 +++++++ src/backend/utils/misc/guc_parameters.dat | 7 + src/backend/utils/misc/guc_tables.c | 1 + src/backend/utils/misc/postgresql.conf.sample | 2 + src/include/access/xlog.h | 1 + src/include/commands/tablecmds.h | 7 + src/include/utils/guc.h | 1 + src/test/recovery/meson.build | 1 + src/test/recovery/t/061_drop_table_logging.pl | 411 ++++++++++++++++++ src/tools/pgindent/typedefs.list | 1 + 14 files changed, 665 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..ab4930fe38e 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8504,6 +8504,67 @@ 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), which is the start LSN of the transaction's commit record. + </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. Operations in prepared + transactions (<command>PREPARE TRANSACTION</command>) are not logged. + </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..7c61f9b9703 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 @@ -2524,6 +2527,7 @@ CommitTransaction(void) AtEOXact_SPI(true); AtEOXact_Enum(); AtEOXact_on_commit_actions(true); + AtEOXact_DropOrTruncateTable(true); AtEOXact_Namespace(true, is_parallel_worker); AtEOXact_SMgr(); AtEOXact_Files(true); @@ -2820,6 +2824,7 @@ PrepareTransaction(void) AtEOXact_SPI(true); AtEOXact_Enum(); AtEOXact_on_commit_actions(true); + AtEOXact_DropOrTruncateTable(false); AtEOXact_Namespace(true, false); AtEOXact_SMgr(); AtEOXact_Files(true); @@ -3051,6 +3056,7 @@ AbortTransaction(void) AtEOXact_SPI(false); AtEOXact_Enum(); AtEOXact_on_commit_actions(false); + AtEOXact_DropOrTruncateTable(false); AtEOXact_Namespace(false, is_parallel_worker); AtEOXact_SMgr(); AtEOXact_Files(false); @@ -5266,6 +5272,8 @@ CommitSubTransaction(void) AtEOSubXact_SPI(true, s->subTransactionId); AtEOSubXact_on_commit_actions(true, s->subTransactionId, s->parent->subTransactionId); + AtEOSubXact_DropOrTruncateTable(true, s->subTransactionId, + s->parent->subTransactionId); AtEOSubXact_Namespace(true, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Files(true, s->subTransactionId, @@ -5440,6 +5448,8 @@ AbortSubTransaction(void) AtEOSubXact_SPI(false, s->subTransactionId); AtEOSubXact_on_commit_actions(false, s->subTransactionId, s->parent->subTransactionId); + AtEOSubXact_DropOrTruncateTable(false, s->subTransactionId, + s->parent->subTransactionId); AtEOSubXact_Namespace(false, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Files(false, s->subTransactionId, 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..22708226def 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" @@ -1388,7 +1389,16 @@ doDeletion(const ObjectAddress *object, int flags) RemoveAttributeById(object->objectId, object->objectSubId); else + { + /* + * RegisterDropOrTruncateTable() decides whether this + * table is logged + */ + if (!(flags & PERFORM_DELETION_INTERNAL)) + RegisterDropOrTruncateTable(object->objectId, false); + heap_drop_with_catalog(object->objectId); + } } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 97e16f3eb47..ec5547d3f42 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -352,6 +352,151 @@ typedef struct ForeignTruncateInfo List *rels; } ForeignTruncateInfo; +/* Structure to hold DROP/ TRUNCATE TABLE information */ +typedef struct DropOrTruncateTableInfo +{ + Oid reloid; + char relname[NAMEDATALEN]; + char schemaname[NAMEDATALEN]; + SubTransactionId subxid; + bool is_truncate; +} DropOrTruncateTableInfo; + +/* Per-transaction list of dropped tables */ +static List *pending_drop_tables = NIL; + +/* + * Register a dropped or truncated table, to log its LSN at commit. + */ +void +RegisterDropOrTruncateTable(Oid reloid, bool is_truncate) +{ + DropOrTruncateTableInfo *info; + MemoryContext oldcontext; + char relkind; + char *relname; + char *schemaname; + + if (!log_object_drops) + return; + + relkind = get_rel_relkind(reloid); + if (relkind != RELKIND_RELATION && relkind != RELKIND_PARTITIONED_TABLE) + return; + if (get_rel_persistence(reloid) != RELPERSISTENCE_PERMANENT) + return; + + relname = get_rel_name(reloid); + schemaname = get_namespace_name(get_rel_namespace(reloid)); + + 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->is_truncate = is_truncate; + + pending_drop_tables = lappend(pending_drop_tables, info); + + MemoryContextSwitchTo(oldcontext); + + pfree(relname); + pfree(schemaname); +} + +/* + * Post-subcommit or post-subabort processing for log_object_drops. + * + * On subcommit, entries registered in this subtransaction become the + * parent's; on subabort they are removed, since those drops never happened. + */ +void +AtEOSubXact_DropOrTruncateTable(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid) +{ + ListCell *lc; + + if (pending_drop_tables == NIL) + return; + + foreach(lc, pending_drop_tables) + { + DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc); + + if (info->subxid != mySubid) + continue; + if (isCommit) + info->subxid = parentSubid; + else + { + pending_drop_tables = foreach_delete_current(pending_drop_tables, lc); + pfree(info); + } + } +} + +/* + * Post-commit or post-abort processing for log_object_drops. + * + * At commit, log every table that was dropped or truncated in this + * transaction, with the start LSN of the commit record. At abort or + * prepare, just forget them. + */ +void +AtEOXact_DropOrTruncateTable(bool isCommit) +{ + ListCell *lc; + + if (pending_drop_tables == NIL) + return; + + /* + * A transaction that changed nothing writes no commit record, for example + * TRUNCATE of a partitioned table with no partitions under wal_level is + * minimal. There is nothing to recover then. + */ + if (isCommit && !XLogRecPtrIsInvalid(XactLastCommitStart)) + { + DropOrTruncateTableInfo *last = llast(pending_drop_tables); + + foreach(lc, pending_drop_tables) + { + DropOrTruncateTableInfo *info = (DropOrTruncateTableInfo *) lfirst(lc); + + 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) ? + 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) ? + errhint("To recover dropped or truncated tables, use " + "recovery_target_lsn = '%X/%08X' with " + "recovery_target_inclusive = false.", + LSN_FORMAT_ARGS(XactLastCommitStart)) : 0)); + } + } + } + + /* Commit, abort and prepare all end with an empty list */ + list_free_deep(pending_drop_tables); + pending_drop_tables = NIL; +} + /* Partial or complete FK creation in addFkConstraint() */ typedef enum addFkConstraintSides { @@ -2174,6 +2319,12 @@ ExecuteTruncateGuts(List *explicit_rels, { Relation rel = (Relation) lfirst(cell); + /* + * RegisterDropOrTruncateTable() decides whether this table is logged. + * Pass "true" because this is a TRUNCATE, not a DROP + */ + RegisterDropOrTruncateTable(RelationGetRelid(rel), true); + /* 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..32718ee1603 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 committed.', + 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..cf9737aa948 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -672,6 +672,8 @@ #log_lock_failures = off # log lock failures #log_recovery_conflict_waits = off # log standby recovery conflict waits # >= deadlock_timeout +#log_object_drops = off # log the recovery LSN of dropped or + # truncated tables #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..130b634ed18 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -32,6 +32,8 @@ extern TupleDesc BuildDescForRelation(const List *columns); extern void RemoveRelations(DropStmt *drop); +extern void RegisterDropOrTruncateTable(Oid reloid, bool is_truncate); + extern Oid AlterTableLookupRelation(AlterTableStmt *stmt, LOCKMODE lockmode); extern void AlterTable(AlterTableStmt *stmt, LOCKMODE lockmode, @@ -99,6 +101,11 @@ extern void AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid, SubTransactionId parentSubid); +extern void AtEOXact_DropOrTruncateTable(bool isCommit); +extern void AtEOSubXact_DropOrTruncateTable(bool isCommit, + SubTransactionId mySubid, + SubTransactionId parentSubid); + extern void RangeVarCallbackMaintainsTable(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); 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..bd56e2749c7 --- /dev/null +++ b/src/test/recovery/t/061_drop_table_logging.pl @@ -0,0 +1,411 @@ +# 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'); + +# Nothing is logged for prepared transactions. + +$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, $any_logged_re), + 0, 'no recovery LSN is logged for a prepared transaction'); +$node->safe_psql('postgres', 'DROP TABLE t_2pc_trunc'); + +# The hint is still logged once when the last registered drop is rolled back. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE hint_a (id int); + CREATE TABLE hint_b (id int); +}); +$log = log_for( + q{ + BEGIN; + DROP TABLE hint_a; + SAVEPOINT s; + DROP TABLE hint_b; + ROLLBACK TO SAVEPOINT s; + COMMIT; +}); +is(count_matches($log, dropped_re('public.hint_a')), + 1, 'drop before the rolled back savepoint is logged'); +is(count_matches($log, $hint_re), + 1, 'hint is logged once when the last drop was rolled back'); +$node->safe_psql('postgres', 'DROP TABLE hint_b'); + +# A rolled back drop does not leak into the next transaction. + +$node->safe_psql( + 'postgres', q{ + CREATE TABLE leak_a (id int); + CREATE TABLE leak_b (id int); +}); +$log = log_for( + q{ + BEGIN; + DROP TABLE leak_a; + ROLLBACK; + DROP TABLE leak_b; +}); +is(count_matches($log, dropped_re('public.leak_a')), + 0, 'rolled back drop is not logged by the next transaction'); +is(count_matches($log, dropped_re('public.leak_b')), + 1, 'drop in the next transaction is logged'); +$node->safe_psql('postgres', 'DROP TABLE leak_a'); + +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
