Thread

1. [PATCH] Fix pg_dump emitting OVERRIDING SYSTEM VALUE for tables with dropped identity columns

   William Bernbaum <wbernbaum@dwdev.com> — 2026-04-17T23:29:05Z

   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?
   
   

2. Re: [PATCH] Fix pg_dump emitting OVERRIDING SYSTEM VALUE for tables with dropped identity columns

   Andreas Karlsson <andreas@proxel.se> — 2026-05-01T07:28:28Z

   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
   
   
   
   
   
   

3. Re: [PATCH] Fix pg_dump emitting OVERRIDING SYSTEM VALUE for tables with dropped identity columns

   Andreas Karlsson <andreas@proxel.se> — 2026-05-01T07:39:07Z

   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