[PATCH v1] Fix for bug #19773 : pg_dump differing generation expressions of inherited columns
Hi,
It was reported in [1]/messages/by-id/19773-887c01a9fda0ec8a@postgresql.org that pg_dump omits a generated column in an
inherited table when the child's expression is different from the
parent.
This seems to happen because pg_dump had special handling for
generation expressions and it only dumped them as part of create
tables. Therefore child expressions which were altered later (or in
cases where only the parent was altered) did not receive the correct
generation expressions.
I'm attaching a patch with a proposed fix. It changes getTableAttrs()
which previously used to always mark 'separate = false' for generation
expressions. That branch has been removed and now those expressions
are handled like any other column. It also changes dumpAttrDef() to
correctly dump the ALTER command with the generation expression.
It also adds an ordering constraint in dumpAttrDef() to handle
multi-level hierarchy and ALTERs in generation expressions at multiple
levels. LLM flagged this as another potential case not reported in the
original report. So the patch explicitly adds ordering to ALTER
statements by adding dependencies.
Please take a look and let me know your thoughts.
Thanks & Regards,
Nitin Motiani
Google
Hi Nitin,
I'm attaching a patch with a proposed fix. It changes getTableAttrs()
which previously used to always mark 'separate = false' for generation
expressions. That branch has been removed and now those expressions
are handled like any other column. It also changes dumpAttrDef() to
correctly dump the ALTER command with the generation expression.
Thanks for the patch. I think emitting a separate ALTER TABLE ONLY
... SET EXPRESSION for the child is the right direction, since it
keeps the column non-local (attislocal = false). Both reported cases
restore with the right expressions and values, for STORED and
VIRTUAL.
I had been tracking the same defect myself, with a somewhat
different fix, so I checked whether the problems I ran into there
also occur with v1 on master (d6cf162f74a). They do, in the cases
below. I have not found a solution for them yet either, so I am only
sharing what I see.
It also adds an ordering constraint in dumpAttrDef() to handle
multi-level hierarchy and ALTERs in generation expressions at multiple
levels.
1. pg_dump does not finish
create table c (x int, z int);
create function h() returns int language sql stable
begin atomic select count(*)::int from c; end;
create table a (x int, z int default h(),
g int generated always as (x * 2) stored);
create table b () inherits (a);
alter table c add column g int generated always as (x * 2) stored;
alter table c inherit b;
Unpatched pg_dump finishes normally. The patched pg_dump keeps
spinning in sortDumpableObjects() (killed by a 20 second alarm,
rc=142). The loop is ("->" meaning "must come after"):
TABLE c -> ATTRDEF b.g new edge: c.g has b.g's expression
ATTRDEF b.g -> TABLE b
TABLE b -> TABLE a INHERITS
TABLE a -> ATTRDEF a.z default h() printed inline
ATTRDEF a.z -> FUNCTION h
FUNCTION h -> TABLE c the body reads c
and the new "child table -> parent attrdef" edge closes it. The
"Indirect loop" repair picks the pair (b, b.g). b.g is already
separate and there is no b -> b.g edge to remove, so it only adds
another b.g -> b edge and the same loop is found again. b.g is not
even dumped here. I could reproduce the same hang when b.g is
dumped (its expression changed on the middle table).
2. Multi-level cases where the child still gets the parent's old
expression
(a) When the loop cannot be resolved:
CREATE TABLE a (x int, g int GENERATED ALWAYS AS (x * 2) STORED);
CREATE TABLE b () INHERITS (a);
CREATE TABLE c () INHERITS (b);
CREATE FUNCTION f3(int) RETURNS int IMMUTABLE LANGUAGE sql
BEGIN ATOMIC
SELECT $1 * 3 + (SELECT count(*)::int FROM public.c WHERE false);
END;
ALTER TABLE b ALTER COLUMN g SET EXPRESSION AS (f3(x));
pg_dump warns "could not resolve dependency loop" (TABLE c, ATTRDEF
b.g, FUNCTION f3) and exits with rc=0. CREATE TABLE c comes before
b's SET EXPRESSION, so after restore c.g is (x * 2) instead of
f3(x). Before the patch the restore failed at b's SET DEFAULT
('column "g" of relation "b" is a generated column'), leaving b.g
and c.g as (x * 2). Now b.g is right, but c.g stays (x * 2) with no
restore error; only pg_dump's loop warning hints at it.
(b) When the parent's column is local:
create table g (a int, g int generated always as (a*2) stored);
create table p (a int, g int);
create function f(x int) returns int immutable language sql
begin atomic select x * 3 + 0 * (select count(*) from p)::int; end;
alter table p drop column g;
alter table p add column g int generated always as (f(a)) stored;
alter table p inherit g;
create table c () inherits (p);
insert into c values (1);
p.g is local, so it is not separate when flagInhAttrs() runs and no
edge is added. The loop repair during sorting makes it separate
later and splits out the SET EXPRESSION. The dump puts CREATE TABLE
c before ALTER TABLE ONLY public.p ... SET EXPRESSION, and the
restored c.g is (a * 2) with the row (1,2), where the source has
f(a) and (1,3). The restore reports no error. Without the patch
SET DEFAULT failed there, leaving both p.g and c.g as (a * 2).
3. The restored child loses its inherited CHECK constraint
CREATE TABLE p (x int, g int GENERATED ALWAYS AS (x * 2) STORED);
CREATE TABLE c () INHERITS (p);
ALTER TABLE ONLY c ALTER COLUMN g SET EXPRESSION AS (x * 9);
ALTER TABLE p ADD CONSTRAINT p_g_check CHECK (g >= 0);
The dump has, after CREATE TABLE public.c:
ALTER TABLE ONLY public.c ALTER COLUMN g SET EXPRESSION AS ((x * 9));
In src and in dst (dumped and restored with the patched pg_dump):
SELECT conrelid::regclass, conname, conislocal, coninhcount
FROM pg_constraint WHERE conname = 'p_g_check' ORDER BY 1;
INSERT INTO c VALUES (-1);
== src
conrelid | conname | conislocal | coninhcount
----------+-----------+------------+-------------
p | p_g_check | t | 0
c | p_g_check | f | 1
(2 rows)
ERROR: new row for relation "c" violates check constraint "p_g_check"
DETAIL: Failing row contains (-1, -9).
== dst
conrelid | conname | conislocal | coninhcount
----------+-----------+------------+-------------
p | p_g_check | t | 0
(1 row)
INSERT 0 1
The restore reports no error. The cause seems to be in the backend:
on master without the patch, running ALTER TABLE ONLY c ... SET
EXPRESSION while the inherited CHECK already exists also drops c's
copy (ATPostAlterTypeCleanup recreates only conislocal constraints).
Inherited NOT NULL constraints lose c's pg_constraint row the same
way (attnotnull stays set, so NULLs are still rejected), and a
merged local+inherited CHECK ends up with coninhcount 0 instead of
1. The patch emits that command whenever a child's expression
differs from its parent's, so the problem now shows up in a plain
dump/restore. A later pg_upgrade of the restored
cluster passes --check and then fails with "child table is missing
constraint".
Since this happens on master without the patch, I think it is
better handled as a separate backend bug, apart from this patch.
Best regards,
Henson
Thanks a lot for the feedback and the edge cases, Henson. I'll look into these.
Regards,
Nitin Motiani
Google
Hi Henson,
I looked further into this. I considered a few approaches to resolve
these edge cases.
The
"Indirect loop" repair picks the pair (b, b.g). b.g is already
separate and there is no b -> b.g edge to remove, so it only adds
another b.g -> b edge and the same loop is found again.
1. One idea I tried was to change the loop repair logic to handle 1 &
2a. This would check the edge direction before picking the edge to
remove. And therefore b.g -> b would would not be picked. Similarly I
also added a new loop repair rule for 2a, for loops that go through
the child -> parent-expression edge.. But I decided against this route
because it seemed likely that more edge cases would need to be found
and fixed. And it would become difficult to maintain.
2. I considered always dumping the child's 'alter ... set expression'
at the end. This would dump them irrespective of whether they matched
the parent or not and avoid any loops. This is similar to how 'set
default' is currently handled for child columns. But this would have
broken the restore to v16 and below as 'set expression' doesn't exist
in the older versions.
3. I decided to avoid adding new dependencies and instead separately
check which child tables needed their own 'alter ... set expression'.
I kept some of the code similar to what I had before. If the child has
a different expression from the parent, its 'alter ... set expression'
is printed anyway because 'separate' is set to true. If a child and
parent have the same expression but that happened because the parent's
own expression is dumped as a separate ALTER, instead of creating an
edge for it, the child would be created before that ALTER runs and
inherit the old expression. Instead of creating an edge, we check it
separately after the tables have been sorted. This seemed the cleanest
approach.
In this, I have created a new function flagInhGenerationExprs which
checks for the cases where a parent has the separate flag set and is
actually dumped. It's run after sorting the tables. I have created a
new field in AttrDefInfo called
maybe_dump_later_if_altered_expression. This is set in flagInhAttrs()
for the cases which might need this special check later. This fixes 1,
2a, and 2b from your cases.
Note this still fails for the following multi-parent scenario.
CREATE TABLE gp (id int, v int, gs int GENERATED ALWAYS AS (v * 3) STORED);
CREATE TABLE p1 () INHERITS (gp);
ALTER TABLE p1 ALTER COLUMN gs SET EXPRESSION AS (v * 7); -- p1: (v *
7), inherited-only
CREATE TABLE p2 (id int, v int, gs int GENERATED ALWAYS AS (v * 7) STORED);
CREATE TABLE c () INHERITS (p1, p2); -- parents agree: both (v * 7)
INSERT INTO c VALUES (1, 1);
This scenario also fails on master and defaults seem to have the same
limitation there. So I haven't tried to fix with any other special
case. Let me know what you think of this. It worked with the first
approach of handling edge cases in loop repair but I thought that
might be risky.
I'm attaching the patch and have added this to commitfest
(https://commitfest.postgresql.org/patch/7519/). Please take a look.
Since this happens on master without the patch, I think it is
better handled as a separate backend bug, apart from this patch.
Regarding this item, are you planning to work on this? If not, I am
happy to take it up.
Thanks & Regards,
Nitin Motiani
Google
Hi Nitin,
I'm attaching the patch and have added this to commitfest
(https://commitfest.postgresql.org/patch/7519/). Please take a look.
Thank you for v2 and for registering it in the CommitFest. I will
find time to review it.
Regarding this item, are you planning to work on this? If not, I am
happy to take it up.
I have a lot of other work going on, so this one was quite far down
my list. I would be very grateful if you took it up, and I will
follow it with interest.
Regards,
Henson
Hi Nitin,
I have looked at v2. Below are my notes on the multi-parent scenario,
and on how the backend problem where the restored child loses its
inherited CHECK constraint shows up with v2.
Note this still fails for the following multi-parent scenario.
[...]
This scenario also fails on master and defaults seem to have the
same limitation there. So I haven't tried to fix with any other
special case. Let me know what you think of this.
I checked this scenario. The restore fails at CREATE TABLE c with:
ERROR: column "gs" inherits conflicting generation expressions
Master fails at the same statement with the same error, so I don't
think this failure takes anything away from the improvement. v2 fixes
the single-parent multi-level cases that silently restored wrong
expressions and values. The multi-parent case is not a new defect
introduced by this patch but a separate one that was already there, so
I agree with leaving it out of scope for this patch.
Since this happens on master without the patch, I think it is
better handled as a separate backend bug, apart from this patch.Regarding this item, are you planning to work on this? If not, I am
happy to take it up.
Thanks again for taking this up. Meanwhile, I checked how this
problem shows up with v2. After a dump and restore with v2, the
expressions come back right, but inherited CHECK constraints are lost:
CREATE TABLE a (x int, g int GENERATED ALWAYS AS (x * 2) STORED);
CREATE TABLE b () INHERITS (a);
CREATE TABLE c () INHERITS (b);
ALTER TABLE b ALTER COLUMN g SET EXPRESSION AS (x * 3);
ALTER TABLE a ADD CONSTRAINT a_chk CHECK (g < 1000);
INSERT INTO c VALUES (1);
v2 emits one command each for b and c:
ALTER TABLE ONLY public.b ALTER COLUMN g
SET EXPRESSION AS ((x * 3));
ALTER TABLE ONLY public.c ALTER COLUMN g
SET EXPRESSION AS ((x * 3));
The restore finishes without errors, but
SELECT conrelid::regclass AS rel, conislocal, coninhcount
FROM pg_constraint WHERE conname = 'a_chk' ORDER BY 1;
gives, in the original and in the restored database:
original restored
rel | conislocal | coninhcount rel | conislocal | coninhcount
-----+------------+------------- -----+------------+-------------
a | t | 0 a | t | 0
b | f | 1
c | f | 1
So in the restored database INSERT INTO c VALUES (500) succeeds and
stores a row with g = 1500, while in the original it fails with "new
row for relation "c" violates check constraint "a_chk"".
Here are the original and the two restores, in execution order.
Original (the script above, without the INSERT):
CREATE TABLE a (x int, g int GENERATED ALWAYS AS (x * 2) STORED);
CREATE TABLE b () INHERITS (a);
CREATE TABLE c () INHERITS (b);
ALTER TABLE b ALTER COLUMN g SET EXPRESSION AS (x * 3);
ALTER TABLE a ADD CONSTRAINT a_chk CHECK (g < 1000);
-- a_chk: a, b, c
Restore (dump without the patch):
CREATE TABLE a (x int, g int GENERATED ALWAYS AS (x * 2) STORED,
CONSTRAINT a_chk CHECK (g < 1000));
CREATE TABLE b () INHERITS (a);
CREATE TABLE c () INHERITS (b);
-- a_chk: a, b, c
-- b.g, c.g: (x * 2)
Restore (v2 dump):
CREATE TABLE a (x int, g int GENERATED ALWAYS AS (x * 2) STORED,
CONSTRAINT a_chk CHECK (g < 1000));
CREATE TABLE b () INHERITS (a);
CREATE TABLE c () INHERITS (b);
ALTER TABLE ONLY b ALTER COLUMN g SET EXPRESSION AS (x * 3);
ALTER TABLE ONLY c ALTER COLUMN g SET EXPRESSION AS (x * 3);
-- a_chk: a
What decides the result is which of the CHECK and the SET EXPRESSION
comes first; ONLY and an inline CHECK make no difference. It also
shows without pg_dump. With a row (1) in b and the expression changed
to (x * 3000):
- original order: ADD CONSTRAINT fails with "check constraint
"a_chk" of relation "b" is violated by some row".
- restore order: ONLY b ... SET EXPRESSION succeeds, b keeps
g = 3000, and b's a_chk is gone.
- a_chk as b's own local CHECK: SET EXPRESSION fails with the same
error.
SET EXPRESSION drops the constraints that depend on the column and
recreates only the local ones (ATPostAlterTypeCleanup()). Inherited
ones are expected to come back when the parent's constraint is
re-added, which does not happen when only the child is altered.
Inherited NOT NULL constraints are lost the same way, as I wrote in my
earlier review. With ALTER TABLE a ALTER COLUMN g SET NOT NULL in
place of the CHECK in the script above, the database restored with v2
has no NOT NULL constraint row for b and c (attnotnull stays set), and
a later DROP NOT NULL on the parent fails with "cache lookup failed
for not-null constraint". So the backend fix needs to cover NOT NULL
as well.
That is, the CHECK loss is not a defect of this patch but a backend
defect of SET EXPRESSION. The CHECK and the SET EXPRESSION should
give the same result in either order, and they do not, and it happens
without pg_dump as well. A dump without the patch does not hit it
only because it never emits SET EXPRESSION.
So I see both the multi-parent case and the loss of inherited CHECK
and NOT NULL constraints as pre-existing defects separate from this
patch. I will look at the backend fix once it is posted.
One small comment on the code: the new field
maybe_dump_later_if_altered_expression is much longer than its
neighbours in AttrDefInfo (adtable, adnum, adef_expr, separate), and
it names what may happen later rather than the state it records. A
shorter name such as inherited_expr, with the existing comment
explaining it, would read better next to separate.
Regards,
Henson
On Tue, Nov 3, 2026 at 11:28 AM Henson Choi <assam258@gmail.com> wrote:
Hi Nitin,
I have looked at v2. Below are my notes on the multi-parent scenario,
and on how the backend problem where the restored child loses its
inherited CHECK constraint shows up with v2.
I didn't look at the whole patch and don't intend to review. I noticed
that this patch adds tests to pg_dump TAP test. Instead, we should
check whether we can leverage 002_pg_upgrade for this testing - if
there are test in regression suite that render the objects in the
required state, leave them in regression database at the end. If the
tests do not do that, probably we are missing respective DDL coverage
in regression suite itself.
--
Best Wishes,
Ashutosh Bapat
On Tue, Nov 3, 2026 at 1:58 PM Henson Choi <assam258@gmail.com> wrote:
Hi Nitin,
I have looked at v2. Below are my notes on the multi-parent scenario,
and on how the backend problem where the restored child loses its
inherited CHECK constraint shows up with v2.
Hi.
+ appendPQExpBuffer(q,
+ "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET EXPRESSION AS (%s);\n",
+ foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1]),
+ adinfo->adef_expr);
ALTER COLUMN SET EXPRESSION
has some problems with dependencies, see [1]/messages/by-id/CACJufxEXY8O5rPLoieo1WoqbuO2ajCEJzVkXcykNtsyij8rEyA@mail.gmail.com and [2]/messages/by-id/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com
[1]: /messages/by-id/CACJufxEXY8O5rPLoieo1WoqbuO2ajCEJzVkXcykNtsyij8rEyA@mail.gmail.com
[2]: /messages/by-id/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com
Hi all,
Thanks for the reviews. v3 is attached, rebased on current master.
One small comment on the code: the new field
maybe_dump_later_if_altered_expression is much longer than its
neighbours in AttrDefInfo (adtable, adnum, adef_expr, separate), and
it names what may happen later rather than the state it records. A
shorter name such as inherited_expr, with the existing comment
explaining it, would read better next to separate.
The only change from v2 is that the flag is renamed to
inherit_unless_altered, as Henson suggested. I wanted to name the flag
such that it's clear to the reader that the decision can change later
depending on whether the expression was altered or not so went with
the original larger name and now this one.
I didn't look at the whole patch and don't intend to review. I noticed
that this patch adds tests to pg_dump TAP test. Instead, we should
check whether we can leverage 002_pg_upgrade for this testing - if
there are test in regression suite that render the objects in the
required state, leave them in regression database at the end. If the
tests do not do that, probably we are missing respective DDL coverage
in regression suite itself.
I tried the 002_pg_upgrade route, but its dump comparison can't catch
this bug. pg_dump leaves out the child's expression in both the
original dump and the dump of the restored database, so the two match
even though the restored child is wrong. pg_upgrade isn't affected
either, since binary-upgrade mode dumps every column with its own
expression. So I've kept the TAP tests, which check pg_dump's output
directly.
On Wed, Nov 4, 2026 at 5:50 PM jian he <jian.universality@gmail.com> wrote:
ALTER COLUMN SET EXPRESSION
has some problems with dependencies, see [1] and [2][1]: /messages/by-id/CACJufxEXY8O5rPLoieo1WoqbuO2ajCEJzVkXcykNtsyij8rEyA@mail.gmail.com
[2]: /messages/by-id/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com
Thanks for the pointers. I tested the v4 patches from [1]/messages/by-id/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com. With either
one, ALTER TABLE ONLY ... SET EXPRESSION fails on a child with
constraints on the column, so restoring such dumps would fail. I'll
also raise it on that thread.
[1]: /messages/by-id/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com
Regards,
Nitin Motiani
Google