Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ8RZ-000000000L4-2YtW for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Oct 2026 11:55:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJ8RW-0000000BdRc-3OSA for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Oct 2026 11:55:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ00I-0000000A5L8-32GV for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 02:55:06 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ00H-00000002tnu-0AWY for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 02:55:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=aI+4RyxNdez+9H4A3pWoqEsKol4kSziVZiOrdr4CIao=; b=ugTy1fY5J8cTamBR4rqZT3DBrX 309ZhfbJ8H7B7N2oVvJV6w+GfSpartQFnQqqtm20VyWCfC7E79euIMKlMHeLC8VgK57JdrA2d1+t+ rio1qPms0C0uRyvm0Zqogj1A3C4nmdn6ulvRv/zKUc4ANAileEfumXqJJgyOxba49IeItwZJ6bncM aupiufG4Tr0uCxy+KGSiIMM11AEGua9Hk0N4r3DXVxBDx7A9U/khXoC3VWodLbyFc2QXMw+zEO4HO ghBNrIc4svR+rPIVTPObQ8jw9wb0/5adgR6/cPiCXUvGRQLeijALPH3uVFEorqvIm67AAl6bDDhn6 iHW1bGfg==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ00G-00000008QYM-3MS6 for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 02:55:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJ00F-00000007aLQ-22vG for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 02:55:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19773: pg_dump omits a different generation expression of an inherited column in a child table To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: kehan5800@gmail.com Reply-To: kehan5800@gmail.com, pgsql-bugs@lists.postgresql.org Date: Tue, 20 Oct 2026 02:54:53 +0000 Message-ID: <19773-887c01a9fda0ec8a@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 "=3D=3D $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): =3D=3D 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) =3D=3D 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 =3D 3; c2: (v * 300), gs =3D 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 =3D 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));