[PATCH] Fix pg_dump emitting OVERRIDING SYSTEM VALUE for tables with dropped identity columns
Hey hackers,
I've encountered a small issue in pg_dump.
It currently emits OVERRIDING SYSTEM VALUE in INSERTs for
a table that doesn't have an identity column if it used to have
a GENERATED ALWAYS AS IDENTITY column that was later dropped.
Simple repro:
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);
pg_dump --data-only --inserts --table=demo mydb
Expected:
INSERT INTO public.demo VALUES (1, 2);
Actual:
INSERT INTO public.demo OVERRIDING SYSTEM VALUE VALUES (1, 2);
The clause is harmless, but it's misleading and causes noisy diffs.
In CI setups that rely on deterministic dumps, this can add significant friction
to reviews. We currently work around it by first clearing the stale markers:
UPDATE pg_catalog.pg_attribute a
SET attidentity = ''
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE a.attrelid = c.oid
AND a.attisdropped
AND a.attidentity <> ''
AND n.nspname = 'public'
AND c.relkind IN ('r', 'p');
Background:
After DROP COLUMN, the pg_attribute row is marked attisdropped = true,
but attidentity still has its old value ('a').
getTableAttrs() loops over all attributes (attnum > 0), including dropped
attributes, and does:
tbinfo->needs_override = tbinfo->needs_override ||
(tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
So a dropped column with attidentity = 'a' still flips needs_override to true,
and dumpTableData_insert() then always emits OVERRIDING SYSTEM VALUE
for the table.
The attached patch fixes this by ignoring dropped columns when setting
needs_override (i.e., checking !attisdropped).
I also considered clearing attidentity in DROP COLUMN, but that wouldn't
address the problem for already-stale catalog entries.
This seems to have been around since identity columns were added.
Patch attached.
Thoughts?
Attachments:
pg_dump-identity-dropped-column.patchapplication/octet-stream; name=pg_dump-identity-dropped-column.patchDownload+62-1
On 4/18/26 01:29, William Bernbaum wrote:
Hey hackers,
I’ve encountered a small issue in pg_dump.
It currently emits OVERRIDING SYSTEM VALUE in INSERTs for
a table that doesn't have an identity column if it used to have
a GENERATED ALWAYS AS IDENTITY column that was later dropped.
[...]
Patch attached.
Thoughts?
Nicely spotted and thanks for the patch! Please add it to the currently
open commitfest (https://commitfest.postgresql.org/59/) so it is not lost.
I have two pieces of feedback:
1. I think the code would be easier to read as
if (!tbinfo->attisdropped[j])
tbinfo->needs_override = tbinfo->needs_override ||
tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS;
or even
if (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS &&
!tbinfo->attisdropped[j])
tbinfo->needs_override = true;
since then we do not get such a long line.
2. While I am not personally a fan of that file it would be more
consistent if the new test was added as part of 002_pg_dump.pl if
possible. Plus then it would mean that we would not need to create and
tear down a PostgreSQL cluster.
Andreas
On 5/1/26 09:28, Andreas Karlsson wrote:
On 4/18/26 01:29, William Bernbaum wrote:
Patch attached.
Forgot this: Maybe it is just a bit too early in the morning and I have
not had coffee yet but I struggled a bit to apply your patch. A
recommendation for making patches easy to apply for other developers is
to generate them with the "git format-patch" command which makes them
easy to apply with "git am". You can use the "-v" flag to "git
format-patch" if you want to add a version number to the generated file
names.
Also another question worth looking into is if this same bug affects
other things, e.g. the setting of hasdefaults.
Thanks again for the patch!
--
Andreas Karlsson
Percona
Thanks for the review, Andreas!
1. I think the code would be easier to read as [...]
Agreed. I went with your second form.
2. [...] it would be more consistent if the new test was added as part
of 002_pg_dump.pl if possible.
Done, and the separate 006_pg_dump_dropped_identity.pl file is gone, so
no extra cluster is created. v2 adds five entries: one that creates a table
whose only identity column is then dropped, one for the COPY output, two
for the --inserts and --column-inserts forms, and one with "like => {}"
asserting that OVERRIDING SYSTEM VALUE never shows up for that table.
Also another question worth looking into is if this same bug affects
other things, e.g. the setting of hasdefaults.
Good question. I checked, and hasdefaults is not affected.
RemoveAttributeById() explicitly clears attgenerated and attnotnull, and
atthasdef is cleared by RemoveAttrDefaultById(), which dependency.c
invokes when the column's pg_attrdef entry is dropped along with the
column (so the table's pg_attrdef rows go away too).
A recommendation for making patches easy to apply [...] git format-patch
Sorry about that, and thanks for the pointer. Attached v2 is generated
with git format-patch -v2 against current master, so git am should be
happy with it.
Now added to the open commitfest:
https://commitfest.postgresql.org/patch/7246/
Best,
Will
On Wed, Sep 2, 2026 at 4:14 AM William Bernbaum <wbernbaum@dwdev.com> wrote:
Sorry about that, and thanks for the pointer. Attached v2 is generated
with git format-patch -v2 against current master, so git am should be
happy with it.
Thanks for updating the patch! LGTM.
The reported issue seems to have existed since v14. So we should backpatch
the patch through v14? Or, since the issue seems harmless in practice,
it's sufficient to commit it only to master as an improvement?
Regards,
--
Fujii Masao
Hi William and everyone,
I was able to reproduce the reported issue on an unpatched PostgreSQL
20devel build.
I used the following scenario:
-Created a table with a GENERATED ALWAYS AS IDENTITY column.
-Inserted a row.
-Dropped the identity column.
-Added the primary key on the remaining columns.
-Ran pg_dump --data-only --inserts.
With the unpatched build, pg_dump produced:
INSERT INTO public.demo OVERRIDING SYSTEM VALUE VALUES (1, 2);
even though the table no longer had an identity column.
I then applied the v2 patch:
v2-0001-pg_dump-Don-t-emit-OVERRIDING-SYSTEM-VALUE-for-dr.patch
The patch applied cleanly, and git diff --check reported no issues.
After rebuilding PostgreSQL, I ran the same pg_dump command again.
This time the output was:
INSERT INTO public.demo VALUES (1, 2);
The OVERRIDING SYSTEM VALUE clause was no longer emitted.
So, based on this test, the patch fixes the reported behavior.
I will also run the added regression tests in
src/bin/pg_dump/t/002_pg_dump.pl to verify the test coverage.
Thanks for the patch and for the clear reproduction case.
Regards,
solai
Hi Fujii,
I'd suggest committing it to master only.
I tried 14, 17 and master. The extra clause is a no-op on restore,
the rows come back the same either way. Nothing is broken, so I don't
see a reason to touch the back branches.
Thanks,
Shihao