pg_dump-identity-dropped-column.patch

From: William Bernbaum <wbernbaum@freezerworks.com>
Subject: [PATCH] Fix pg_dump emitting OVERRIDING SYSTEM VALUE for tables
 with dropped identity columns

When a column with GENERATED ALWAYS AS IDENTITY is dropped via
ALTER TABLE ... DROP COLUMN, PostgreSQL marks the column as dropped
(attisdropped = true) but does not clear pg_attribute.attidentity.

In getTableAttrs(), pg_dump processes all attributes (attnum > 0),
including dropped ones.  The needs_override flag is then set based
on attidentity without checking attisdropped, causing
dumpTableData_insert() to emit OVERRIDING SYSTEM VALUE for tables
that no longer have any identity column.

Fix by moving the attisdropped evaluation before the needs_override
calculation and gating the check on !attisdropped.

---
 src/bin/pg_dump/pg_dump.c                          |  5 +++--
 src/bin/pg_dump/t/008_pg_dump_dropped_identity.pl  | 55 ++++++++++++++++++++++
 2 files changed, 57 insertions(+), 2 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -9370,8 +9370,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 			tbinfo->typstorage[j] = *(PQgetvalue(res, r, i_typstorage));
 			tbinfo->attidentity[j] = *(PQgetvalue(res, r, i_attidentity));
 			tbinfo->attgenerated[j] = *(PQgetvalue(res, r, i_attgenerated));
-			tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
 			tbinfo->attisdropped[j] = (PQgetvalue(res, r, i_attisdropped)[0] == 't');
+			tbinfo->needs_override = tbinfo->needs_override || (!tbinfo->attisdropped[j] && tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
 			tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen));
 			tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign));
 			tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't');

diff --git a/src/bin/pg_dump/t/008_pg_dump_dropped_identity.pl b/src/bin/pg_dump/t/008_pg_dump_dropped_identity.pl
new file mode 100644
--- /dev/null
+++ b/src/bin/pg_dump/t/008_pg_dump_dropped_identity.pl
@@ -0,0 +1,55 @@
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Verify that pg_dump does not emit OVERRIDING SYSTEM VALUE for a table
+# whose only identity column has been dropped.
+
+my $node = PostgreSQL::Test::Cluster->new('identity_override');
+$node->init;
+$node->start;
+
+# Create a table with an identity column, insert a row, then drop the
+# identity column and switch to a composite primary key.
+$node->safe_psql('postgres', q{
+    CREATE TABLE demo (
+        id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+        fk_a BIGINT NOT NULL,
+        fk_b BIGINT NOT NULL
+    );
+
+    INSERT INTO demo (fk_a, fk_b)
+    OVERRIDING SYSTEM VALUE VALUES (1, 2);
+
+    ALTER TABLE demo DROP COLUMN id;
+    ALTER TABLE demo ADD PRIMARY KEY (fk_a, fk_b);
+});
+
+# Dump data with INSERT statements
+my $dumpfile = $node->basedir . '/dump.sql';
+
+command_ok(
+    [
+        'pg_dump',
+        '--data-only',
+        '--inserts',
+        '--table=demo',
+        '--file=' . $dumpfile,
+        'postgres'
+    ],
+    'pg_dump with --inserts runs');
+
+my $dump = slurp_file($dumpfile);
+
+# Ensure no spurious OVERRIDING SYSTEM VALUE is emitted
+unlike(
+    $dump,
+    qr/OVERRIDING SYSTEM VALUE/,
+    'no OVERRIDING SYSTEM VALUE for dropped identity column'
+);
+
+# Ensure the row is still dumped correctly
+like(
+    $dump,
+    qr/insert into.*demo.*1.*2/i,
+    'row is dumped correctly'
+);
+
+done_testing();