pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19773: pg_dump omits a different generation expression of an inherited column in a child table
2+ messages / 2 participants
[nested] [flat]
* BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table
@ 2026-10-20 02:54 PG Bug reporting form <noreply@postgresql.org>
2026-10-21 18:39 ` Re: BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-10-20 02:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kehan5800@gmail.com
The following bug has been logged on the website:
Bug reference: 19773
Logged by: Ke
Email address: kehan5800@gmail.com
PostgreSQL version: 18.6
Operating system: Ubuntu 22.04 x86_64 (Linux 5.15)
Description:
Description:
If an inheritance child (CREATE TABLE ... INHERITS) has a generated column
that it inherits from the parent, and the child's generation expression
differs from the parent's, pg_dump does not write the child's expression.
That happens after ALTER TABLE ONLY parent ALTER COLUMN ... SET EXPRESSION,
or after ALTER TABLE child ALTER COLUMN ... SET EXPRESSION. On restore the
child gets the parent's expression, and because COPY does not include
generated columns, every stored value in the child is recomputed with the
wrong expression. pg_dump and the restore report no error or warning.
The documentation says that "the child column can have a different
generation expression", so the source database is valid; the dump just
does not preserve it.
Script (plain format; pg_dump -Fc + pg_restore gives the same result):
createdb src
createdb dst
psql -X -q -d src <<'SQL'
-- case 1: change only the parent; the child keeps (v * 3)
CREATE TABLE p (id int, v int, gs int GENERATED ALWAYS AS (v * 3) STORED);
CREATE TABLE c () INHERITS (p);
INSERT INTO c VALUES (1, 1);
ALTER TABLE ONLY p ALTER COLUMN gs SET EXPRESSION AS (v * 5);
-- case 2: change the child's expression
CREATE TABLE p2 (id int, v int, gs int GENERATED ALWAYS AS (v * 3) STORED);
CREATE TABLE c2 () INHERITS (p2);
ALTER TABLE c2 ALTER COLUMN gs SET EXPRESSION AS (v * 300);
INSERT INTO c2 VALUES (2, 1);
SQL
pg_dump src > src.sql
psql -X -q -d dst -f src.sql
for db in src dst; do
echo "== $db"
psql -X -d $db -c "SELECT tableoid::regclass AS tbl, id, v, gs FROM ONLY c
UNION ALL SELECT tableoid::regclass, id, v, gs FROM ONLY c2 ORDER BY id" \
-c "SELECT adrelid::regclass AS tbl, pg_get_expr(adbin,
adrelid) AS expr FROM pg_attrdef ORDER BY adrelid::regclass::text"
done
grep -A3 '^CREATE TABLE public.c2\? ' src.sql
Output (master 45fc51fcbf; 17.11, 18.6 and 19rc1 give the same):
== src
tbl | id | v | gs
-----+----+---+-----
c | 1 | 1 | 3
c2 | 2 | 1 | 300
(2 rows)
tbl | expr
-----+-----------
c | (v * 3)
c2 | (v * 300)
p | (v * 5)
p2 | (v * 3)
(4 rows)
== dst
tbl | id | v | gs
-----+----+---+----
c | 1 | 1 | 5
c2 | 2 | 1 | 3
(2 rows)
tbl | expr
-----+---------
c | (v * 5)
c2 | (v * 3)
p | (v * 5)
p2 | (v * 3)
(4 rows)
CREATE TABLE public.c (
)
INHERITS (public.p);
--
CREATE TABLE public.c2 (
)
INHERITS (public.p2);
Expected: dst should have the same expressions and values as src
(c: (v * 3), gs = 3; c2: (v * 300), gs = 300).
On 18 and later the same thing happens with VIRTUAL generated columns: after
ALTER TABLE c2 ALTER COLUMN gv SET EXPRESSION AS (v * 400) on a child whose
parent has gv int GENERATED ALWAYS AS (v * 4) VIRTUAL, the restored child
returns 4 instead of 400.
Versions tested (SELECT version()):
PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(master 45fc51fcbf, 2026-10-17)
PostgreSQL 19rc1 (REL_19_STABLE 10a0dcfc25)
PostgreSQL 18.6 (REL_18_STABLE 4732a5c53b)
PostgreSQL 17.11 (REL_17_STABLE 65e944d92b)
pg_dump, psql and the server were the same version in each run. ALTER
COLUMN ... SET EXPRESSION was added in 17 (commit 5d06e99a3c), so 17 is the
first affected release.
Cause, as far as I can tell:
flagInhAttrs() in src/bin/pg_dump/common.c notices that the child's
expression differs from the parent's (foundDiffGenerated) and keeps the
child's attrdef dumpable. But getTableAttrs() in pg_dump.c sets
attrdefs[j].separate = false for generation expressions ("there is no
syntax for it"), so the expression can only be printed inline in the
child's CREATE TABLE column list. dumpTableSchema() prints a column there
only if shouldPrintColumn() is true, i.e. attislocal or the table is a
partition. In an INHERITS child the column is not local, and SET EXPRESSION
does not make it local, so the expression is never printed. Partitions and
children whose column is local (for example declared in CREATE TABLE ...
INHERITS, or attached with ALTER TABLE ... INHERIT) are dumped correctly;
--binary-upgrade prints all columns and is not affected.
Since 17 there is syntax that pg_dump could emit for this case, similar to
what it already does with ALTER TABLE ONLY ... ALTER COLUMN ... SET DEFAULT
for an inherited column with a different default:
ALTER TABLE ONLY public.c2 ALTER COLUMN gs SET EXPRESSION AS ((v * 300));
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table
2026-10-20 02:54 BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-21 18:39 ` Nitin Motiani <nitinmotiani@google.com>
0 siblings, 0 replies; 2+ messages in thread
From: Nitin Motiani @ 2026-10-21 18:39 UTC (permalink / raw)
To: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
Hi,
Thanks for the report and the analysis. I have proposed a patch for
this on pgsql-hackers[1]. Please take a look.
Thanks,
Nitin Motiani
Google
[1] https://www.postgresql.org/message-id/flat/CAH5HC97y7PFE6rJh8kVDUszPxGQE030nZvyHu%3DwZT7FBkOV3qA%40m...
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-21 18:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-20 02:54 BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table PG Bug reporting form <noreply@postgresql.org>
2026-10-21 18:39 ` Nitin Motiani <nitinmotiani@google.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox