From 448bfca1f2c3b58e7ca0dee9be26a54a4ae1ce95 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 10 Aug 2026 16:14:56 +0800 Subject: [PATCH v3 1/1] Fix dependency issue when directly ALTER TABLE SET EXPRESSION on a partition issues when ALTER COLUMN ... SET EXPRESSION is applied directly to a partition: 1. If the generated column has an index dependency, we may need to rebuild the index, which means dropping and rebuilding the entire index hierarchy across all partitions, which is not ideal. 2. If we rebuild constraints (e.g. CHECK) on the parent partitioned table then let is cascades to all child table, the whole partition hierarchy ends up needing an ACCESS EXCLUSIVE lock. That seems excessive when we're only trying to change the generation expression on a single partition. (Might need more discussion on this point.) New idea for fix this: For columns referenced only by NOT NULL, CHECK, or FOREIGN KEY constraints (no index dependencies): changing the generation expression only affects the underlying column data, not its type. So instead of rebuilding these constraints, we can just re-validate them against the newly computed data. This avoids unnecessary constraint rebuilds and gets rid of the ACCESS EXCLUSIVE lock requirement on the whole partition hierarchy when we're only touching one partition. commitfest: https://commitfest.postgresql.org/patch/7117 discussion: https://postgr.es/m/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=ACCJyytWZg@mail.gmail.com --- src/backend/commands/tablecmds.c | 239 +++++++++++++++++- .../regress/expected/generated_stored.out | 59 +++++ .../regress/expected/generated_virtual.out | 52 ++++ src/test/regress/sql/generated_stored.sql | 43 ++++ src/test/regress/sql/generated_virtual.sql | 41 +++ 5 files changed, 421 insertions(+), 13 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 2fa534413ea..acb068e0b54 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -205,6 +205,9 @@ typedef struct AlteredTableInfo /* Objects to rebuild after completing ALTER TYPE operations */ List *changedConstraintOids; /* OIDs of constraints to rebuild */ List *changedConstraintDefs; /* string definitions of same */ + List *changedConstraintRelids; /* OIDs of relation that + * associated constraints to + * rebuild */ List *changedIndexOids; /* OIDs of indexes to rebuild */ List *changedIndexDefs; /* string definitions of same */ char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */ @@ -794,6 +797,9 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, static List *collectPartitionIndexExtDeps(List *partitionOids); static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState); static void freePartitionIndexExtDeps(List *extDepState); +static void RemoveChangedCosntr(AlteredTableInfo *tab, Relation rel); +static void ATPrepSetExpression(List **wqueue, AlteredTableInfo *tab, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode); /* ---------------------------------------------------------------- * DefineRelation @@ -5126,6 +5132,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + ATPrepSetExpression(wqueue, tab, rel, cmd, recurse, recursing, lockmode); pass = AT_PASS_SET_EXPRESSION; break; case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */ @@ -8807,18 +8814,8 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, CommandCounterIncrement(); } - /* - * Find everything that depends on the column (constraints, indexes, etc), - * and record enough information to let us recreate the objects. - */ - RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); - - /* - * Find whole-row referenced objects that depend on the column - * (constraints, indexes, etc.), and record enough information to let us - * recreate the objects. - */ - RememberWholeRowDependentForRebuilding(tab, AT_SetExpression, rel); + if (tab->subcmds[AT_PASS_ALTER_TYPE] == NIL) + RemoveChangedCosntr(tab, rel); /* * Drop the dependency records of the GENERATED expression, in particular @@ -8880,6 +8877,208 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, return address; } +static void +RemoveChangedCosntr(AlteredTableInfo *tab, Relation rel) +{ + for (int conpos = 0; conpos < list_length(tab->changedConstraintOids); conpos++) + { + Form_pg_constraint con; + + Oid constrOid = list_nth_oid(tab->changedConstraintOids, conpos); + + HeapTuple contuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constrOid)); + + if (!HeapTupleIsValid(contuple)) + elog(ERROR, "cache lookup failed for constraint %u", constrOid); + + con = (Form_pg_constraint) GETSTRUCT(contuple); + + if (con->conrelid != RelationGetRelid(rel)) + { + ReleaseSysCache(contuple); + continue; + } + + if (!con->convalidated || !con->conenforced) + { + tab->changedConstraintOids = list_delete_nth_cell(tab->changedConstraintOids, conpos); + tab->changedConstraintDefs = list_delete_nth_cell(tab->changedConstraintDefs, conpos); + conpos--; + ReleaseSysCache(contuple); + continue; + } + + switch (con->contype) + { + case CONSTRAINT_NOTNULL: + tab->verify_new_notnull = true; + break; + case CONSTRAINT_CHECK: + { + NewConstraint *newcon; + Datum val; + char *conbin; + + newcon = palloc0_object(NewConstraint); + newcon->name = pstrdup(NameStr(con->conname)); + newcon->contype = CONSTR_CHECK; + + val = SysCacheGetAttrNotNull(CONSTROID, contuple, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + + /* + * Phase3 will call expand_generated_columns_in_expr, no + * need do it here + */ + newcon->qual = stringToNode(conbin); + + tab->constraints = lappend(tab->constraints, newcon); + break; + } + case CONSTRAINT_FOREIGN: + if (rel->rd_rel->relkind == RELKIND_RELATION) + { + NewConstraint *newcon; + Constraint *fkconstraint; + + fkconstraint = makeNode(Constraint); + fkconstraint->contype = CONSTRAINT_FOREIGN; + /* ->conname determined below */ + fkconstraint->conname = pstrdup(NameStr(con->conname)); + fkconstraint->deferrable = con->condeferrable; + fkconstraint->initdeferred = con->condeferred; + fkconstraint->location = -1; + fkconstraint->pktable = NULL; + fkconstraint->pk_attrs = NIL; + fkconstraint->fk_matchtype = con->confmatchtype; + fkconstraint->fk_upd_action = con->confupdtype; + fkconstraint->fk_del_action = con->confdeltype; + fkconstraint->fk_del_set_cols = NIL; + fkconstraint->old_conpfeqop = NIL; + fkconstraint->old_pktable_oid = InvalidOid; + fkconstraint->is_enforced = con->conenforced; + fkconstraint->skip_validation = false; + fkconstraint->initially_valid = con->convalidated; + fkconstraint->fk_with_period = con->conperiod; + + /* + * Tell Phase 3 to check that the constraint is satisfied + * by existing rows. We can skip this during table + * creation, when constraint is specified as NOT ENFORCED, + * or when requested explicitly by specifying NOT VALID in + * an ADD FOREIGN KEY command, and when we're recreating a + * constraint following a SET DATA TYPE operation that did + * not impugn its validity. + */ + newcon = palloc0_object(NewConstraint); + newcon->name = pstrdup(NameStr(con->conname)); + newcon->contype = CONSTR_FOREIGN; + newcon->refrelid = con->confrelid; + newcon->refindid = con->conindid; + newcon->conid = con->oid; + newcon->conwithperiod = fkconstraint->fk_with_period; + newcon->qual = (Node *) fkconstraint; + + tab->constraints = lappend(tab->constraints, newcon); + } + break; + + default: + ReleaseSysCache(contuple); + continue; + } + tab->changedConstraintOids = list_delete_nth_cell(tab->changedConstraintOids, conpos); + tab->changedConstraintDefs = list_delete_nth_cell(tab->changedConstraintDefs, conpos); + conpos--; + ReleaseSysCache(contuple); + } +} + +/* + * ALTER TABLE ALTER COLUMN SET EXPRESSION + */ +static void +ATPrepSetExpression(List **wqueue, AlteredTableInfo *tab, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode) +{ + char *colName = cmd->name; + Form_pg_attribute attTup; + AttrNumber attnum; + + HeapTuple tuple = SearchSysCacheAttName(RelationGetRelid(rel), + colName); + + if (!HeapTupleIsValid(tuple)) + { + /* + * it can happen, for example: ------------------------------------- + * ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (2) + * STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3); + */ + return; + } + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + if (attTup->attgenerated == ATTRIBUTE_GENERATED_STORED) + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + + ReleaseSysCache(tuple); + + /* + * Find everything that depends on the column (constraints, indexes, etc), + * and record enough information to let us recreate the objects. + */ + RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); + + /* + * Find whole-row referenced objects that depend on the column + * (constraints, indexes, etc.), and record enough information to let us + * recreate the objects. + */ + RememberWholeRowDependentForRebuilding(tab, AT_SetExpression, rel); + + if (tab->changedConstraintOids != NIL || tab->changedIndexOids != NIL) + { + List *children = find_inheritance_children(RelationGetRelid(rel), NoLock); + + /* + * Cannot use ONLY to modify a generated column's expression when + * there are dependencies (e.g. constraints, indexes) that need to be + * rebuilt, since that rebuild must cascade to the children too. + */ + if (!recurse && !recursing && children != NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table"), + errdetail("Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY.")); + + /* cannot rebuild partition child table index */ + if (!recursing) + { + if (rel->rd_rel->relispartition && + tab->changedIndexOids != NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects"), + errdetail("Inherited objects, such as indexes, cannot be rebuilt independently for a child table.")); + + /* + * Cannot drop generation expression from inherited columns. + * + * + * TODO: local constraints should be OK. probally need islocal + */ + if (!rel->rd_rel->relispartition && has_superclass(RelationGetRelid(rel))) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects"), + errdetail("Inherited objects, such as indexes and constraints, cannot be rebuilt independently for a child table.")); + } + } +} + /* * ALTER TABLE ALTER COLUMN DROP EXPRESSION */ @@ -16006,18 +16205,29 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab) /* OK, capture the constraint's existing definition string */ char *defstring = pg_get_constraintdef_command(conoid); Oid indoid; + HeapTuple tuple; + Form_pg_constraint con; + + tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(conoid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for constraint %u", + conoid); + + con = (Form_pg_constraint) GETSTRUCT(tuple); /* * It is critical to create not-null constraints ahead of primary key * indexes; otherwise, the not-null constraint would be created by the * primary key, and the constraint name would be wrong. */ - if (get_constraint_type(conoid) == CONSTRAINT_NOTNULL) + if (con->contype == CONSTRAINT_NOTNULL) { tab->changedConstraintOids = lcons_oid(conoid, tab->changedConstraintOids); tab->changedConstraintDefs = lcons(defstring, tab->changedConstraintDefs); + tab->changedConstraintRelids = lcons_oid(con->conrelid, + tab->changedConstraintRelids); } else { @@ -16026,7 +16236,10 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab) conoid); tab->changedConstraintDefs = lappend(tab->changedConstraintDefs, defstring); + tab->changedConstraintRelids = lappend_oid(tab->changedConstraintRelids, + con->conrelid); } + ReleaseSysCache(tuple); /* * For the index of a constraint, if any, remember if it is used for diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index fd6caf1cf2d..6fac7ad24ec 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -1683,6 +1683,65 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); c | integer | | | x | integer | | | generated always as (b * 2) stored +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +DROP INDEX gtest35_b_idx; +SELECT array_agg(oid order by oid)::text AS gtest36conoids +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2') \gset +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ERROR: check constraint "cc" of relation "gtest35_1" is violated by some row +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a + 110); -- foreign constraint violation +ERROR: insert or update on table "gtest35_1" violates foreign key constraint "gtest35_fk" +DETAIL: Key (b)=(115) is not present in table "gtest_pk". +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (nullif(a, 8)); -- error +ERROR: column "b" of relation "gtest35_1" contains null values +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- ok +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ERROR: check constraint "cc" of relation "gtest35_2" is violated by some row +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 111 else a + 2 end); -- error +ERROR: insert or update on table "gtest35_2" violates foreign key constraint "gtest35_fk" +DETAIL: Key (b)=(111) is not present in table "gtest_pk". +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+2); -- ok +ALTER TABLE ONLY gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+3); -- ok +SELECT array_agg(oid order by oid)::text = :'gtest36conoids' as nochange +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2'); + nochange +---------- + t +(1 row) + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +NOTICE: merging multiple inherited definitions of column "a" +NOTICE: merging multiple inherited definitions of column "b" +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes and constraints, cannot be rebuilt independently for a child table. -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value CREATE TABLE gtest_rule (a int, b int GENERATED ALWAYS AS (a * 2) STORED); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 6ee029796f1..9147bdac27c 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1577,6 +1577,58 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); c | integer | | | x | integer | | | generated always as (b * 2) +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +-- CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +-- ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +-- DROP INDEX gtest35_b_idx; +SELECT array_agg(oid order by oid)::text AS gtest36conoids +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2') \gset +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ERROR: check constraint "cc" of relation "gtest35_1" is violated by some row +-- ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a + 110); -- foreign constraint violation +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (nullif(a, 8)); -- error +ERROR: column "b" of relation "gtest35_1" contains null values +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- ok +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ERROR: check constraint "cc" of relation "gtest35_2" is violated by some row +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 111 else a + 2 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+2); -- ok +ALTER TABLE ONLY gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+3); -- ok +SELECT array_agg(oid order by oid)::text = :'gtest36conoids' as nochange +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2'); + nochange +---------- + t +(1 row) + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +NOTICE: merging multiple inherited definitions of column "a" +NOTICE: merging multiple inherited definitions of column "b" +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +-- CREATE INDEX gtest36_b_idx ON gtest36(b); +-- ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes and constraints, cannot be rebuilt independently for a child table. -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value CREATE TABLE gtest_rule (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index 9eecd13dd9e..7e23f4100dd 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -844,6 +844,49 @@ ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +DROP INDEX gtest35_b_idx; + +SELECT array_agg(oid order by oid)::text AS gtest36conoids +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2') \gset + +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a + 110); -- foreign constraint violation +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (nullif(a, 8)); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- ok +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 111 else a + 2 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+2); -- ok +ALTER TABLE ONLY gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+3); -- ok + +SELECT array_agg(oid order by oid)::text = :'gtest36conoids' as nochange +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2'); + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index ed9d50fe784..e510aefd05d 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -844,6 +844,47 @@ ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +-- CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +-- ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +-- DROP INDEX gtest35_b_idx; + +SELECT array_agg(oid order by oid)::text AS gtest36conoids +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2') \gset + +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +-- ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a + 110); -- foreign constraint violation +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (nullif(a, 8)); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- ok +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 111 else a + 2 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+2); -- ok +ALTER TABLE ONLY gtest35_2 ALTER COLUMN b SET EXPRESSION AS (a+3); -- ok + +SELECT array_agg(oid order by oid)::text = :'gtest36conoids' as nochange +FROM pg_constraint +WHERE conrelid::regclass in ('gtest35', 'gtest35_1', 'gtest35_2'); + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +-- CREATE INDEX gtest36_b_idx ON gtest36(b); +-- ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value -- 2.34.1