Hi, It was reported in [1] 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 [1] https://www.postgresql.org/message-id/flat/19773-887c01a9fda0ec8a%40postgresql.org
From 8bcee6e5a4ccaa319f6fd7163c3a0ebe149883e0 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> Date: Wed, 21 Oct 2026 17:45:26 +0000 Subject: [PATCH v1] pg_dump: Dump differing generation expressions of inherited columns. Since v16, a child table's generated column may have a generation expression different from its parent's, and since v17, ALTER TABLE ... SET EXPRESSION can produce that state for a column the child inherited without a local definition. pg_dump did not handle this: it assumed generation expressions could only be dumped as part of the column definition, but it does not print inherited columns in the child's CREATE TABLE, so the child's expression was silently lost and the restored child used the parent's expression instead. Fix by treating generation expressions like defaults: if the column is not printed, dump the expression as a separate ALTER TABLE ONLY ... SET EXPRESSION command. Also make a child table depend on a parent's separately dumped expression if the child relies on inheriting it, so that the child is created only after the parent's expression is final. Reported-by: Ke <[email protected]> --- src/bin/pg_dump/common.c | 32 ++++++++++++++- src/bin/pg_dump/pg_dump.c | 69 +++++++++++++++++++++----------- src/bin/pg_dump/t/002_pg_dump.pl | 49 +++++++++++++++++++++++ 3 files changed, 126 insertions(+), 24 deletions(-) diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index cda9d73a9dd..7472949208d 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -473,6 +473,9 @@ flagInhIndexes(Archive *fout, TableInfo tblinfo[], int numTables) * we are in binary upgrade mode, we dump such expressions anyway because * in those cases inherited tables are recreated standalone first and then * reattached to the parent. (See also the logic in dumpTableSchema().) + * A child's expression that differs from the parent's is dumped, either + * as part of the column definition or, if the column is inherited without + * a local definition, as a separate ALTER TABLE ... SET EXPRESSION. * * modifies tblinfo */ @@ -639,10 +642,37 @@ flagInhAttrs(Archive *fout, DumpOptions *dopt, TableInfo *tblinfo, int numTables tbinfo->attrdefs[j] = attrDef; } - /* No need to dump generation expression if it's inheritable */ + /* + * No need to dump generation expression if it's inheritable. + * + * However, if a parent's expression is itself dumped as a + * separate ALTER TABLE ... SET EXPRESSION command (because the + * parent inherited the column, too, but has a different + * expression than its own parents), the child table must be + * created after that command has run, or else the child would + * inherit the parent's original expression. Add a dependency to + * enforce that. + */ if (foundSameGenerated && !foundDiffGenerated && !tbinfo->ispartition && !dopt->binary_upgrade) + { tbinfo->attrdefs[j]->dobj.dump = DUMP_COMPONENT_NONE; + + for (k = 0; k < numParents; k++) + { + TableInfo *parent = parents[k]; + int inhAttrInd; + + inhAttrInd = strInArray(tbinfo->attnames[j], + parent->attnames, + parent->numatts); + if (inhAttrInd >= 0 && + parent->attrdefs[inhAttrInd] != NULL && + parent->attrdefs[inhAttrInd]->separate) + addObjectDependency(&tbinfo->dobj, + parent->attrdefs[inhAttrInd]->dobj.dumpId); + } + } } } } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 69171d6b859..edd5a004522 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -9607,19 +9607,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) * is to put it into the CREATE command, but in some cases that's * not possible. */ - if (tbinfo->attgenerated[adnum - 1]) - { - /* - * Column generation expressions cannot be dumped separately, - * because there is no syntax for it. By setting separate to - * false here we prevent the "default" from being processed as - * its own dumpable object. Later, flagInhAttrs() will mark - * it as not to be dumped at all, if possible (that is, if it - * can be inherited from a parent). - */ - attrdefs[j].separate = false; - } - else if (tbinfo->relkind == RELKIND_VIEW) + if (tbinfo->relkind == RELKIND_VIEW) { /* * Defaults on a VIEW must always be dumped as separate ALTER @@ -9629,7 +9617,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } else if (!shouldPrintColumn(dopt, tbinfo, adnum - 1)) { - /* column will be suppressed, print default separately */ + /* + * Column will be suppressed, print default separately. For a + * generation expression, this means ALTER TABLE ... SET + * EXPRESSION; but flagInhAttrs() will usually mark it as not + * to be dumped at all, since an inherited column normally has + * the same expression as its parent. + */ attrdefs[j].separate = true; } else @@ -18097,6 +18091,11 @@ dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo) /* * dumpAttrDef --- dump an attribute's default-value declaration + * + * For a generated column, this emits ALTER TABLE ... SET EXPRESSION rather + * than SET DEFAULT. We get here for such a column only if it is inherited + * without a local definition (so it's not printed in CREATE TABLE) and its + * expression differs from the parent's; see flagInhAttrs(). */ static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo) @@ -18114,9 +18113,17 @@ dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo) if (!dopt->dumpSchema) return; - /* Skip if not "separate"; it was dumped in the table's definition */ + /* + * Skip if not "separate"; it was dumped in the table's definition. That + * requires the column itself to be printed there, else the default (or + * generation expression) would be lost entirely. + */ if (!adinfo->separate) + { + Assert(!(adinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) || + shouldPrintColumn(dopt, tbinfo, adnum - 1)); return; + } q = createPQExpBuffer(); delq = createPQExpBuffer(); @@ -18125,17 +18132,33 @@ dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo) foreign = tbinfo->relkind == RELKIND_FOREIGN_TABLE ? "FOREIGN " : ""; - appendPQExpBuffer(q, - "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET DEFAULT %s;\n", - foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1]), - adinfo->adef_expr); + if (tbinfo->attgenerated[adnum - 1]) + { + /* + * No drop statement: DROP EXPRESSION would turn the column into a + * regular column (and isn't allowed on an inherited column anyway). + * The expression goes away with the table. + */ + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET EXPRESSION AS (%s);\n", + foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1]), + adinfo->adef_expr); + } + else + { + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET DEFAULT %s;\n", + foreign, qualrelname, fmtId(tbinfo->attnames[adnum - 1]), + adinfo->adef_expr); - appendPQExpBuffer(delq, "ALTER %sTABLE %s ALTER COLUMN %s DROP DEFAULT;\n", - foreign, qualrelname, - fmtId(tbinfo->attnames[adnum - 1])); + appendPQExpBuffer(delq, "ALTER %sTABLE %s ALTER COLUMN %s DROP DEFAULT;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[adnum - 1])); + } tag = psprintf("%s %s", tbinfo->dobj.name, tbinfo->attnames[adnum - 1]); + /* delq is empty for a generated column (see above): no drop statement */ if (adinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId, ARCHIVE_OPTS(.tag = tag, @@ -18144,7 +18167,7 @@ dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo) .description = "DEFAULT", .section = SECTION_PRE_DATA, .createStmt = q->data, - .dropStmt = delq->data)); + .dropStmt = delq->len > 0 ? delq->data : NULL)); pfree(tag); destroyPQExpBuffer(q); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index c89d699e816..f880733e39a 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -4006,6 +4006,55 @@ my %tests = ( }, }, + # Inherited (non-local) generated columns whose expressions were changed + # with SET EXPRESSION must have those expressions dumped separately. + 'CREATE TABLE test_table_generated_child3 (differing expressions)' => { + create_order => 4, + create_sql => 'CREATE TABLE dump_test.test_table_generated_child3 () + INHERITS (dump_test.test_table_generated); + ALTER TABLE dump_test.test_table_generated_child3 + ALTER COLUMN col2 SET EXPRESSION AS (col1 * 4), + ALTER COLUMN col3 SET EXPRESSION AS (col1 * 5);', + regexp => qr/^ + \QCREATE TABLE dump_test.test_table_generated_child3 (\E\n + \)\n + \QINHERITS (dump_test.test_table_generated);\E\n + /xms, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { + binary_upgrade => 1, + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + + 'ALTER TABLE test_table_generated_child3 SET EXPRESSION (stored)' => { + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_generated_child3 ALTER COLUMN col2 SET EXPRESSION AS ((col1 * 4));\E\n + /xm, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { + binary_upgrade => 1, + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + + 'ALTER TABLE test_table_generated_child3 SET EXPRESSION (virtual)' => { + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_generated_child3 ALTER COLUMN col3 SET EXPRESSION AS ((col1 * 5));\E\n + /xm, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { + binary_upgrade => 1, + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + 'CREATE TABLE table_with_stats' => { create_order => 98, create_sql => 'CREATE TABLE dump_test.table_index_stats ( --
