On Thu, Oct 15, 2026 at 11:48 AM shihao zhong <[email protected]> wrote: > > 3. Dumping the child's constraint as local changes the catalog. With > your patch, k's c comes back with conislocal = true, so ALTER TABLE p > DROP CONSTRAINT c no longer drops it from k. pg_upgrade keeps it > inherited today, and your patch makes it local there too. ALTER > CONSTRAINT is the only command that gives back an inherited > constraint that only the child enforces. >
https://www.postgresql.org/message-id/202410011520.zl4go2gzgzwn%40alvherre.pgsql mentioned in some case, it's ok for pg_dump changing conislocal status. Maybe it does not apply to this case. However, my guess is that for the SQL below, some constraint is bound to change conislocal. because adding not valid constraint happens very later of pg_dump. drop table if exists p3 cascade; create table p3 (a int); alter table p3 add constraint c check (a > 0) not valid; create table k3 () inherits (p3); > Your partition case is real, my 0003 missed it, and pg_upgrade loses > it on master too. v2-0003 dumps that constraint before its ancestors' > ones, as your patch does, with your test. I added you as co-author. > > A local NOT VALID child that sorts after the parent still comes back > validated. That comes from ADD CONSTRAINT merging into the inherited > NOT ENFORCED copy, which Nikolay's patch fixes [1]. > > [1] > https://postgr.es/m/cam527d8qch4q1xv-tftdmm3ebcbie9axfdttthpuogwnyt0...@mail.gmail.com > There are 2 more bugs related after playing around with your v2-0003. Bug A affects PG18 and earlier (reproduced on PG16). The following scirpt pg_dump output is wrong. drop table if exists p3 cascade; create table p3 (a int); alter table p3 add constraint c check (a > 0) not valid; create table k3 () inherits (p3); alter table k3 validate constraint c; ---------------------------------------------------- Bug B affects PG18. Restoring the pg_dump output of the script below into an existing cluster fails. drop table if exists tp, tc cascade; create table tp (a int); create table tc (a int) inherits (tp); alter table tc add constraint nn not null a not valid; alter table tp alter column a set not null; BUGB root cause is https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e I am using pg_dump --table-and-children=pattern --no-owner --no-acl --clean --no-data for testing. Since these bugs are related, posting them in this thread seems appropriate. ---------------------------------------------------------------------------------------- > 2. ALTER COLUMN TYPE on the parent turns the child's constraint back > to NOT ENFORCED. The child's constraint is dropped and recreated from > the parent's definition. This issue dates back to at least PG14. See https://dbfiddle.uk/AO112Bxp. I think we need a unified way to restore the old constraint's properties. The attached WIP patch only makes ALTER COLUMN SET DATA TYPE and ALTER COLUMN SET EXPRESSION preserve pg_constraint.conenforced and convalidated of a child's constraint. It does not try to preserve any other constraint properties. Since this is an old bug, I tend to think it's not an open item for PG19. -- jian https://www.enterprisedb.com/
From 4a27d216f560f0038a49d818b9ac1e02016779e5 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Fri, 16 Oct 2026 15:58:51 +0800 Subject: [PATCH v3 1/1] Preserve child constraint property in ALTER TYPE and SET EXPRESSION ALTER COLUMN SET DATA TYPE and ALTER COLUMN SET EXPRESSION drop the constraints that depend on the column and recreate them. A child table's inherited constraint is recreated by recursing from the parent's constraint, so it gets the parent's state, not its own. As a result, a child constraint that was VALID can become NOT VALID afterwards. --- src/backend/commands/tablecmds.c | 278 +++++++++++++++++- src/test/regress/expected/foreign_key.out | 132 +++++++++ .../regress/expected/generated_stored.out | 81 +++++ .../regress/expected/generated_virtual.out | 81 +++++ src/test/regress/expected/inherit.out | 88 ++++++ src/test/regress/sql/foreign_key.sql | 84 ++++++ src/test/regress/sql/generated_stored.sql | 45 +++ src/test/regress/sql/generated_virtual.sql | 45 +++ src/test/regress/sql/inherit.sql | 55 ++++ src/tools/pgindent/typedefs.list | 1 + 10 files changed, 885 insertions(+), 5 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 93ad49e59fd..f020b9b910e 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -203,6 +203,7 @@ typedef struct AlteredTableInfo /* Objects to rebuild after ALTER TYPE or SET EXPRESSION operations */ List *changedConstraintOids; /* OIDs of constraints to rebuild */ List *changedConstraintDefs; /* string definitions of same */ + List *changedConstrInfo; /* List of ConstrInfo */ List *changedIndexOids; /* OIDs of indexes to rebuild */ List *changedIndexDefs; /* string definitions of same */ char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */ @@ -226,6 +227,26 @@ typedef struct NewConstraint ExprState *qualstate; /* Execution state for CHECK expr */ } NewConstraint; +/* + * Struct describing an inherited check, not-null or foreign key constraint + * dropped by ALTER COLUMN SET DATA TYPE or ALTER COLUMN SET EXPRESSION. It is + * recreated by recursing from the parent's constraint, but the child's + * constraint may have been enforced or validated while the parent's is not, and + * a not-null or foreign key constraint may have its own name, so we remember + * the child's name and state. + */ +typedef struct ConstrInfo +{ + Oid conrelid; /* Relation the constraint is on */ + ConstrType contype; /* CONSTR_CHECK, CONSTR_NOTNULL or + * CONSTR_FOREIGN */ + char *conname; /* Constraint name */ + AttrNumber attnum; /* Column number, if not-null */ + char *parentname; /* Parent constraint's name, if foreign key */ + bool conenforced; /* Was it enforced? */ + bool convalidated; /* Was it validated? */ +} ConstrInfo; + /* * Struct describing one new column value that needs to be computed during * Phase 3 copy (this could be either a new column with a non-null default, or @@ -668,6 +689,8 @@ static void RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterT static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab); static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab); static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab); +static void RememberPartitionForeignKeys(AlteredTableInfo *tab, Relation rel, + AttrNumber attnum); static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode); static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, Oid ownerId, @@ -756,7 +779,9 @@ static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, const char *compression); static char GetAttributeStorage(Oid atttypid, const char *storagemode); - +static void ATPrepSetExpression(List **wqueue, AlteredTableInfo *tab, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, + LOCKMODE lockmode); /* ---------------------------------------------------------------- * DefineRelation @@ -5087,6 +5112,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 */ @@ -8686,6 +8712,44 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE return address; } +/* + * 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; + + tuple = SearchSysCacheAttName(RelationGetRelid(rel), + colName); + if (!HeapTupleIsValid(tuple)) + { + /* + * If a newly added generated column has its generation expression set + * in the same command, the column has not been installed yet, so the + * attribute lookup will return false. + */ + return; + } + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + ReleaseSysCache(tuple); + + /* + * Like ALTER TYPE, SET EXPRESSION drops and recreates the constraints on + * the column. This is called for each partition too, by + * ATSimpleRecursion. + */ + RememberPartitionForeignKeys(tab, rel, attnum); +} + + /* * ALTER TABLE ALTER COLUMN SET EXPRESSION * @@ -10017,6 +10081,53 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, ATSimplePermissions(AT_AddConstraint, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); + /* + * When re-adding a constraint after ALTER COLUMN SET DATA TYPE or ALTER + * COLUMN SET EXPRESSION, a child table's copy of it may have been + * enforced or validated while the parent's is not, and a child's not-null + * constraint may have its own name. Recreate it with the child's own + * name and state, as remembered by ATPostAlterTypeCleanup. Validated + * constraints are queued for verification below, as usual. + * + * A child's CHECK constraint is never weaker than its parent's, so there + * is nothing to look up if the parent's is enforced and valid. Not-null + * constraints are always looked up, because of the name. + */ + if (is_readd && recursing && + (constr->contype == CONSTR_NOTNULL || + constr->contype == CONSTR_CHECK)) + { + AttrNumber attnum = InvalidAttrNumber; + + /* not-null constraints are matched by column, CHECK ones by name */ + if (constr->contype == CONSTR_NOTNULL) + attnum = get_attnum(RelationGetRelid(rel), + strVal(linitial(constr->keys))); + + foreach_ptr(ConstrInfo, oldcon, tab->changedConstrInfo) + { + if (oldcon->conrelid != RelationGetRelid(rel) || + oldcon->contype != constr->contype) + continue; + + if (constr->contype == CONSTR_NOTNULL) + { + if (oldcon->attnum != attnum) + continue; + } + else if (constr->conname == NULL || + strcmp(oldcon->conname, constr->conname) != 0) + continue; + + constr = copyObject(constr); + constr->conname = oldcon->conname; + constr->is_enforced = oldcon->conenforced; + constr->initially_valid = oldcon->convalidated; + constr->skip_validation = !oldcon->convalidated; + break; + } + } + /* * Call AddRelationNewConstraints to do the work, making sure it works on * a copy of the Constraint so transformExpr can't modify the original. It @@ -11206,6 +11317,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, { PartitionDesc pd = RelationGetPartitionDesc(rel, true); Relation trigrel; + char *parentname = NULL; /* * Triggers of the foreign keys will be manipulated a bunch of times @@ -11215,6 +11327,18 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, */ trigrel = table_open(TriggerRelationId, RowExclusiveLock); + /* + * When re-adding a foreign key after ALTER TYPE or SET EXPRESSION, a + * partition's copy of it may have been validated while the parent's + * is not, and may have its own name. RememberPartitionForeignKeys + * remembered them by the name of their parent constraint. + */ + if (OidIsValid(fkconstraint->old_pktable_oid)) + { + Assert(wqueue); + parentname = get_constraint_name(parentConstr); + } + /* * Recurse to take appropriate action on each partition; either we * find an existing constraint to reparent to ours, or we create a new @@ -11227,6 +11351,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, AttrMap *attmap; AttrNumber mapped_fkattnum[INDEX_MAX_KEYS]; bool attached; + Constraint *partfkconstraint = fkconstraint; ObjectAddress address; CheckAlterTableIsSafe(partition); @@ -11264,11 +11389,41 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, continue; } + /* + * Recreate a partition's foreign key with its own name and state. + * Work on a copy, since fkconstraint is also used for the other + * partitions. + */ + if (parentname != NULL) + { + AlteredTableInfo *partitiontab; + + /* Find or create work queue entry for this table */ + partitiontab = ATGetQueueEntry(wqueue, partition); + + foreach_ptr(ConstrInfo, oldcon, + partitiontab->changedConstrInfo) + { + if (oldcon->contype == CONSTR_FOREIGN && + oldcon->conrelid == RelationGetRelid(partition) && + strcmp(oldcon->parentname, parentname) == 0) + { + partfkconstraint = copyObject(fkconstraint); + partfkconstraint->conname = oldcon->conname; + partfkconstraint->is_enforced = oldcon->conenforced; + partfkconstraint->initially_valid = oldcon->convalidated; + partfkconstraint->skip_validation = !oldcon->convalidated; + break; + } + } + } + /* * No luck finding a good constraint to reuse; create our own. */ address = addFkConstraint(addFkReferencingSide, - fkconstraint->conname, fkconstraint, + partfkconstraint->conname, + partfkconstraint, partition, pkrel, indexOid, parentConstr, numfks, pkattnum, mapped_fkattnum, pfeqoperators, @@ -11277,7 +11432,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, with_period); /* call ourselves to finalize the creation and we're done */ - addFkRecurseReferencing(wqueue, fkconstraint, partition, pkrel, + addFkRecurseReferencing(wqueue, partfkconstraint, partition, pkrel, indexOid, address.objectId, numfks, @@ -15028,6 +15183,8 @@ ATPrepAlterColumnType(List **wqueue, tab->newvals = lappend(tab->newvals, newval); if (ATColumnChangeRequiresRewrite(transform, attnum)) tab->rewrite |= AT_REWRITE_COLUMN_REWRITE; + + RememberPartitionForeignKeys(tab, rel, attnum); } else if (transform) ereport(ERROR, @@ -16097,6 +16254,79 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab) } } +/* + * Subroutine for ATPrepAlterColumnType and ATPrepSetExpression: remember the + * name and state of a partition's foreign keys on column attnum. + * + * Such a foreign key is dropped together with its parent's foreign key, before + * the partition's own ALTER COLUMN SET DATA TYPE or ALTER COLUMN SET EXPRESSION + * is executed, so we must do this in Phase 1. addFkRecurseReferencing + * recreates it the way it was. Skip the rows for the referenced side's + * partitions, which are on the same relation as their parent. + */ +static void +RememberPartitionForeignKeys(AlteredTableInfo *tab, Relation rel, + AttrNumber attnum) +{ + if (!rel->rd_rel->relispartition) + return; + + foreach_node(ForeignKeyCacheInfo, fk, RelationGetFKeyList(rel)) + { + HeapTuple contup; + HeapTuple parenttup; + Form_pg_constraint con; + Form_pg_constraint parentcon; + bool found = false; + + for (int i = 0; i < fk->nkeys; i++) + { + if (fk->conkey[i] == attnum) + found = true; + } + if (!found) + continue; + + contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid)); + if (!HeapTupleIsValid(contup)) /* should not happen */ + elog(ERROR, "cache lookup failed for constraint %u", fk->conoid); + con = (Form_pg_constraint) GETSTRUCT(contup); + if (!OidIsValid(con->conparentid)) + { + ReleaseSysCache(contup); + continue; + } + + parenttup = SearchSysCache1(CONSTROID, + ObjectIdGetDatum(con->conparentid)); + if (!HeapTupleIsValid(parenttup)) /* should not happen */ + elog(ERROR, "cache lookup failed for constraint %u", + con->conparentid); + parentcon = (Form_pg_constraint) GETSTRUCT(parenttup); + + /* + * Skip the rows for the referenced table's partitions, whose parent + * is on the same relation. + */ + if (parentcon->conrelid != con->conrelid) + { + ConstrInfo *oldcon = palloc0_object(ConstrInfo); + + oldcon->conrelid = con->conrelid; + oldcon->contype = CONSTR_FOREIGN; + oldcon->conname = pstrdup(NameStr(con->conname)); + oldcon->parentname = pstrdup(NameStr(parentcon->conname)); + oldcon->conenforced = con->conenforced; + oldcon->convalidated = con->convalidated; + + tab->changedConstrInfo = lappend(tab->changedConstrInfo, oldcon); + } + + ReleaseSysCache(parenttup); + ReleaseSysCache(contup); + } +} + /* * Cleanup after we've finished all the ALTER TYPE or SET EXPRESSION * operations for a particular relation. We have to drop and recreate all the @@ -16161,6 +16391,43 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) } confrelid = con->confrelid; conislocal = con->conislocal; + + /* + * Remember the name and state of an inherited constraint of this + * table, so that ATAddCheckNNConstraint can recreate it that way. A + * not-null constraint is also remembered by column, because its name + * can differ from the parent's. + * + * The child's constraint is recreated by recursing from the parent's, + * which gives it the parent's state. That loses the child's own + * state in some cases, for example if it is ENFORCED while the + * parent's is NOT ENFORCED, or VALID while the parent's is NOT VALID. + * + * Those cases are rare, but rather than look up the parent's + * constraint to detect them, we simply remember every inherited + * constraint. + */ + if (con->coninhcount > 0 && relid == tab->relid && + (con->contype == CONSTRAINT_NOTNULL || + con->contype == CONSTRAINT_CHECK)) + { + ConstrInfo *oldcon = palloc0_object(ConstrInfo); + + oldcon->conrelid = relid; + oldcon->conname = pstrdup(NameStr(con->conname)); + if (con->contype == CONSTRAINT_NOTNULL) + { + oldcon->contype = CONSTR_NOTNULL; + oldcon->attnum = extractNotNullColumn(tup); + } + else + oldcon->contype = CONSTR_CHECK; + + oldcon->conenforced = con->conenforced; + oldcon->convalidated = con->convalidated; + tab->changedConstrInfo = lappend(tab->changedConstrInfo, + oldcon); + } ReleaseSysCache(tup); ObjectAddressSet(obj, ConstraintRelationId, oldId); @@ -16170,8 +16437,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) * If the constraint is inherited (only), we don't want to inject a * new definition here; it'll get recreated when * ATAddCheckNNConstraint recurses from adding the parent table's - * constraint. But we had to carry the info this far so that we can - * drop the constraint below. + * constraint, with the name and state we remembered above. But we + * had to carry the info this far so that we can drop the constraint + * below. */ if (!conislocal) continue; diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index b1f36f6a1bb..5aa33e00d0f 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2116,6 +2116,138 @@ WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY conname COLLAT (5 rows) DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk; +-- ALTER COLUMN SET DATA TYPE should not changing foreign key constraints +CREATE TABLE fk_notpartitioned_pk (a int PRIMARY KEY); +CREATE TABLE fk_partitioned_fk (k int, a int) PARTITION BY LIST (k); +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fkey + FOREIGN KEY (a) REFERENCES fk_notpartitioned_pk NOT VALID; +CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES IN (1); +ALTER TABLE fk_partitioned_fk_1 VALIDATE CONSTRAINT fkey; +-- attached with its own valid foreign key +CREATE TABLE fk_partitioned_fk_2 (k int, + a int CONSTRAINT fkey2 REFERENCES fk_notpartitioned_pk); +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (2); +-- validated on one leaf partition only +CREATE TABLE fk_partitioned_fk_3 PARTITION OF fk_partitioned_fk FOR VALUES IN (3, 4) + PARTITION BY LIST (k); +CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES IN (3); +CREATE TABLE fk_partitioned_fk_3_2 PARTITION OF fk_partitioned_fk_3 FOR VALUES IN (4); +ALTER TABLE fk_partitioned_fk_3_1 VALIDATE CONSTRAINT fkey; +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + conrelid | conname | coninhcount | convalidated | childconstr +-----------------------+---------+-------------+--------------+------------- + fk_partitioned_fk | fkey | 0 | f | f + fk_partitioned_fk_1 | fkey | 1 | t | t + fk_partitioned_fk_2 | fkey2 | 1 | t | t + fk_partitioned_fk_3 | fkey | 1 | f | t + fk_partitioned_fk_3_1 | fkey | 1 | t | t + fk_partitioned_fk_3_2 | fkey | 1 | f | t +(6 rows) + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a TYPE bigint; +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + conrelid | conname | coninhcount | convalidated | childconstr +-----------------------+---------+-------------+--------------+------------- + fk_partitioned_fk | fkey | 0 | f | f + fk_partitioned_fk_1 | fkey | 1 | t | t + fk_partitioned_fk_2 | fkey2 | 1 | t | t + fk_partitioned_fk_3 | fkey | 1 | f | t + fk_partitioned_fk_3_1 | fkey | 1 | t | t + fk_partitioned_fk_3_2 | fkey | 1 | f | t +(6 rows) + +-- ALTER COLUMN SET EXPRESSION should not changing foreign key constraints +ALTER TABLE fk_partitioned_fk DROP COLUMN a; +ALTER TABLE fk_partitioned_fk ADD COLUMN a int GENERATED ALWAYS AS (k) STORED; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fkey + FOREIGN KEY (a) REFERENCES fk_notpartitioned_pk NOT VALID; +ALTER TABLE fk_partitioned_fk_1 VALIDATE CONSTRAINT fkey; +ALTER TABLE fk_partitioned_fk_3_1 VALIDATE CONSTRAINT fkey; +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + conrelid | conname | coninhcount | convalidated | childconstr +-----------------------+---------+-------------+--------------+------------- + fk_partitioned_fk | fkey | 0 | f | f + fk_partitioned_fk_1 | fkey | 1 | t | t + fk_partitioned_fk_2 | fkey | 1 | f | t + fk_partitioned_fk_3 | fkey | 1 | f | t + fk_partitioned_fk_3_1 | fkey | 1 | t | t + fk_partitioned_fk_3_2 | fkey | 1 | f | t +(6 rows) + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a SET EXPRESSION AS (k * 1); +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + conrelid | conname | coninhcount | convalidated | childconstr +-----------------------+---------+-------------+--------------+------------- + fk_partitioned_fk | fkey | 0 | f | f + fk_partitioned_fk_1 | fkey | 1 | t | t + fk_partitioned_fk_2 | fkey | 1 | f | t + fk_partitioned_fk_3 | fkey | 1 | f | t + fk_partitioned_fk_3_1 | fkey | 1 | t | t + fk_partitioned_fk_3_2 | fkey | 1 | f | t +(6 rows) + +DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk; +-- When the referenced table is partitioned, a partition's own foreign key has +-- a child row for each referenced partition, on the same relation. Those are +-- not mistaken for the partition's copy of the parent's foreign key, even if +-- both foreign keys have the same name. +CREATE TABLE fk_partitioned_pk (a int PRIMARY KEY) PARTITION BY LIST (a); +CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES IN (1); +CREATE TABLE fk_partitioned_pk_2 PARTITION OF fk_partitioned_pk FOR VALUES IN (2); +CREATE TABLE fk_partitioned_fk (k int, a int) PARTITION BY LIST (k); +CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES IN (1); +ALTER TABLE fk_partitioned_fk_1 ADD CONSTRAINT fk + FOREIGN KEY (a) REFERENCES fk_partitioned_pk ON DELETE CASCADE; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk + FOREIGN KEY (a) REFERENCES fk_partitioned_pk NOT VALID; +SELECT c.conrelid::regclass, c.conname, p.conname AS parent, c.convalidated +FROM pg_constraint c LEFT JOIN pg_constraint p ON p.oid = c.conparentid +WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY c.conrelid::regclass::text COLLATE "C", c.conname COLLATE "C"; + conrelid | conname | parent | convalidated +---------------------+---------+--------+-------------- + fk_partitioned_fk | fk | | f + fk_partitioned_fk | fk_3 | fk | f + fk_partitioned_fk | fk_4 | fk | f + fk_partitioned_fk_1 | fk | | t + fk_partitioned_fk_1 | fk_1 | fk | t + fk_partitioned_fk_1 | fk_2 | fk | t + fk_partitioned_fk_1 | fk_5 | fk | f +(7 rows) + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a TYPE bigint; +SELECT c.conrelid::regclass, c.confrelid::regclass, c.conname, p.conname AS parent, c.convalidated +FROM pg_constraint c LEFT JOIN pg_constraint p ON p.oid = c.conparentid +WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY c.conrelid::regclass::text COLLATE "C", c.conname COLLATE "C"; + conrelid | confrelid | conname | parent | convalidated +---------------------+---------------------+---------+--------+-------------- + fk_partitioned_fk | fk_partitioned_pk | fk | | f + fk_partitioned_fk | fk_partitioned_pk_1 | fk_1 | fk | f + fk_partitioned_fk | fk_partitioned_pk_2 | fk_2 | fk | f + fk_partitioned_fk_1 | fk_partitioned_pk | fk | | t + fk_partitioned_fk_1 | fk_partitioned_pk_1 | fk_3 | fk | t + fk_partitioned_fk_1 | fk_partitioned_pk_2 | fk_4 | fk | t + fk_partitioned_fk_1 | fk_partitioned_pk | fk_5 | fk | f +(7 rows) + +DROP TABLE fk_partitioned_fk, fk_partitioned_pk; -- NOT VALID and NOT ENFORCED foreign key on a non-partitioned table -- referencing a partitioned table CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b); diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index fd6caf1cf2d..d67e9bafb33 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -783,6 +783,49 @@ ERROR: column "c" of relation "gtestnn_childdef" contains null values ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error ERROR: column "c" of relation "gtestnn_child" contains null values ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok +ALTER TABLE gtestnn_parent ALTER COLUMN c DROP NOT NULL; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con1 NOT NULL c NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con2 CHECK (c > 0) NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con3 CHECK (c > 0) NOT ENFORCED; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con1; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con2; +ALTER TABLE gtestnn_child ALTER CONSTRAINT con3 ENFORCED; +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +----------------+----------------------------+------------+-------------+-------------- + gtestnn_child | con1 | f | t | t + gtestnn_child | con2 | f | t | t + gtestnn_child | con3 | f | t | t + gtestnn_child | gtestnn_parent_f3_not_null | f | t | t + gtestnn_parent | con1 | t | t | f + gtestnn_parent | con2 | t | t | f + gtestnn_parent | con3 | t | f | f + gtestnn_parent | gtestnn_parent_f3_not_null | t | t | t +(8 rows) + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtestnn_parent ALTER COLUMN c SET EXPRESSION AS (nullif(f1, 4) + nullif(f2, 7)); +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +----------------+----------------------------+------------+-------------+-------------- + gtestnn_child | con1 | f | t | t + gtestnn_child | con2 | f | t | t + gtestnn_child | con3 | f | t | t + gtestnn_child | gtestnn_parent_f3_not_null | f | t | t + gtestnn_parent | con1 | t | t | f + gtestnn_parent | con2 | t | t | f + gtestnn_parent | con3 | t | f | f + gtestnn_parent | gtestnn_parent_f3_not_null | t | t | t +(8 rows) + -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE); INSERT INTO gtest22a VALUES (2); @@ -1447,6 +1490,44 @@ Inherits: gtest30_1 ROLLBACK; +ALTER TABLE gtest30 ADD CONSTRAINT con1 NOT NULL b NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con2 CHECK (b > 0) NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con3 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con1; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con2; +ALTER TABLE gtest30_1 ALTER CONSTRAINT con3 ENFORCED; +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +-----------+---------+------------+-------------+-------------- + gtest30 | con1 | t | t | f + gtest30 | con2 | t | t | f + gtest30 | con3 | t | f | f + gtest30_1 | con1 | f | t | t + gtest30_1 | con2 | f | t | t + gtest30_1 | con3 | f | t | t +(6 rows) + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 3); +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +-----------+---------+------------+-------------+-------------- + gtest30 | con1 | t | t | f + gtest30 | con2 | t | t | f + gtest30 | con3 | t | f | f + gtest30_1 | con1 | f | t | t + gtest30_1 | con2 | f | t | t + gtest30_1 | con3 | f | t | t +(6 rows) + -- test drop expression with subpartitions CREATE TABLE gtest_root (a int, b int, c int GENERATED ALWAYS AS (a + b) STORED) PARTITION BY LIST (a); CREATE TABLE gtest_node PARTITION OF gtest_root FOR VALUES IN (1) PARTITION BY LIST (b); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index b2f8e16e63b..50c50fd371c 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -812,6 +812,49 @@ ERROR: column "c" of relation "gtestnn_childdef" contains null values ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) VIRTUAL; -- error ERROR: column "c" of relation "gtestnn_child" contains null values ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok +ALTER TABLE gtestnn_parent ALTER COLUMN c DROP NOT NULL; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con1 NOT NULL c NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con2 CHECK (c > 0) NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con3 CHECK (c > 0) NOT ENFORCED; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con1; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con2; +ALTER TABLE gtestnn_child ALTER CONSTRAINT con3 ENFORCED; +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +----------------+----------------------------+------------+-------------+-------------- + gtestnn_child | con1 | f | t | t + gtestnn_child | con2 | f | t | t + gtestnn_child | con3 | f | t | t + gtestnn_child | gtestnn_parent_f3_not_null | f | t | t + gtestnn_parent | con1 | t | t | f + gtestnn_parent | con2 | t | t | f + gtestnn_parent | con3 | t | f | f + gtestnn_parent | gtestnn_parent_f3_not_null | t | t | t +(8 rows) + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtestnn_parent ALTER COLUMN c SET EXPRESSION AS (nullif(f1, 4) + nullif(f2, 7)); +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +----------------+----------------------------+------------+-------------+-------------- + gtestnn_child | con1 | f | t | t + gtestnn_child | con2 | f | t | t + gtestnn_child | con3 | f | t | t + gtestnn_child | gtestnn_parent_f3_not_null | f | t | t + gtestnn_parent | con1 | t | t | f + gtestnn_parent | con2 | t | t | f + gtestnn_parent | con3 | t | f | f + gtestnn_parent | gtestnn_parent_f3_not_null | t | t | t +(8 rows) + -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); ERROR: unique constraints on virtual generated columns are not supported @@ -1382,6 +1425,44 @@ Inherits: ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error ERROR: cannot drop generation expression from inherited column +ALTER TABLE gtest30 ADD CONSTRAINT con1 NOT NULL b NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con2 CHECK (b > 0) NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con3 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con1; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con2; +ALTER TABLE gtest30_1 ALTER CONSTRAINT con3 ENFORCED; +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +-----------+---------+------------+-------------+-------------- + gtest30 | con1 | t | t | f + gtest30 | con2 | t | t | f + gtest30 | con3 | t | f | f + gtest30_1 | con1 | f | t | t + gtest30_1 | con2 | f | t | t + gtest30_1 | con3 | f | t | t +(6 rows) + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 3); +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + relname | conname | conislocal | conenforced | convalidated +-----------+---------+------------+-------------+-------------- + gtest30 | con1 | t | t | f + gtest30 | con2 | t | t | f + gtest30 | con3 | t | f | f + gtest30_1 | con1 | f | t | t + gtest30_1 | con2 | f | t | t + gtest30_1 | con3 | f | t | t +(6 rows) + -- composite type dependencies CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text); CREATE TABLE gtest31_2 (x int, y gtest31_1); diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 0136aa53c96..cb5522ecfba 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1615,6 +1615,94 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); alter table p1_c1 inherit p1; ERROR: constraint "p1_a_check" conflicts with NOT ENFORCED constraint on child table "p1_c1" drop table p1, p1_c1; +-- ALTER COLUMN SET DATA TYPE recreates an inherited constraint with the child's +-- own state, which can be ENFORCED or VALID while the parent's is not +create temp view constr_status_now as +select conrelid::regclass as relname, conname, conislocal, coninhcount, + conenforced, convalidated +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'pp', 'pp1', 'pp11'); +create table p1(f1 int); +alter table p1 add constraint p1_nn not null f1 not valid; +alter table p1 add constraint p1_a_check check (f1 > 0) not valid; +alter table p1 add constraint p1_b_check check (f1 < 100) not enforced; +create table p1_c1() inherits (p1); +alter table p1_c1 validate constraint p1_nn; +alter table p1_c1 validate constraint p1_a_check; +alter table p1_c1 alter constraint p1_b_check enforced; +create table p1_c2(f1 int constraint p1_a_check check (f1 > 0)) inherits (p1); +NOTICE: merging column "f1" with inherited definition +NOTICE: merging constraint "p1_a_check" with inherited definition +select * from constr_status_now +order by relname::text collate "C", conname collate "C"; + relname | conname | conislocal | coninhcount | conenforced | convalidated +---------+------------+------------+-------------+-------------+-------------- + p1 | p1_a_check | t | 0 | t | f + p1 | p1_b_check | t | 0 | f | f + p1 | p1_nn | t | 0 | t | f + p1_c1 | p1_a_check | f | 1 | t | t + p1_c1 | p1_b_check | f | 1 | t | t + p1_c1 | p1_nn | f | 1 | t | t + p1_c2 | p1_a_check | t | 1 | t | t + p1_c2 | p1_b_check | f | 1 | f | f + p1_c2 | p1_nn | f | 1 | t | t +(9 rows) + +create temp table constr_status as table constr_status_now; +alter table p1 alter column f1 type bigint; +NOTICE: merging constraint "p1_a_check" with inherited definition +-- ALTER COLUMN SET DATA TYPE should not change any constraint's status +(table constr_status except table constr_status_now) +union all +(table constr_status_now except table constr_status); + relname | conname | conislocal | coninhcount | conenforced | convalidated +---------+---------+------------+-------------+-------------+-------------- +(0 rows) + +drop table constr_status; +drop table p1 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table p1_c1 +drop cascades to table p1_c2 +-- same for partitions, and an ENFORCED NOT VALID one is not validated +create table pp(k int, f1 int) partition by list (k); +alter table pp add constraint pp_a_check check (f1 > 0) not valid; +alter table pp add constraint pp_b_check check (f1 < 100) not enforced; +create table pp1(k int, f1 int) partition by list (k); +create table pp11(k int, f1 int); +alter table pp11 add constraint pp_a_check check (f1 > 0); +alter table pp11 add constraint pp_b_check check (f1 < 100) not valid; +alter table pp1 attach partition pp11 for values in (1); +alter table pp1 add constraint pp_a_check check (f1 > 0) not valid; +NOTICE: merging constraint "pp_a_check" with inherited definition +alter table pp1 add constraint pp_b_check check (f1 < 100) not enforced; +NOTICE: merging constraint "pp_b_check" with inherited definition +alter table pp attach partition pp1 for values in (1); +select * from constr_status_now +order by relname::text collate "C", conname collate "C"; + relname | conname | conislocal | coninhcount | conenforced | convalidated +---------+------------+------------+-------------+-------------+-------------- + pp | pp_a_check | t | 0 | t | f + pp | pp_b_check | t | 0 | f | f + pp1 | pp_a_check | f | 1 | t | f + pp1 | pp_b_check | f | 1 | f | f + pp11 | pp_a_check | f | 1 | t | t + pp11 | pp_b_check | f | 1 | t | f +(6 rows) + +create temp table constr_status as table constr_status_now; +alter table pp alter column f1 type bigint; +-- ALTER COLUMN SET DATA TYPE should not change any constraint's status +(table constr_status except table constr_status_now) +union all +(table constr_status_now except table constr_status); + relname | conname | conislocal | coninhcount | conenforced | convalidated +---------+---------+------------+-------------+-------------+-------------- +(0 rows) + +drop table constr_status; +drop table pp; +drop view constr_status_now; -- -- Test DROP behavior of multiply-defined CHECK constraints -- diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 99ea110e848..f0289a5fb36 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1604,6 +1604,90 @@ WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY conname COLLAT DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk; +-- ALTER COLUMN SET DATA TYPE should not changing foreign key constraints +CREATE TABLE fk_notpartitioned_pk (a int PRIMARY KEY); +CREATE TABLE fk_partitioned_fk (k int, a int) PARTITION BY LIST (k); +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fkey + FOREIGN KEY (a) REFERENCES fk_notpartitioned_pk NOT VALID; +CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES IN (1); +ALTER TABLE fk_partitioned_fk_1 VALIDATE CONSTRAINT fkey; +-- attached with its own valid foreign key +CREATE TABLE fk_partitioned_fk_2 (k int, + a int CONSTRAINT fkey2 REFERENCES fk_notpartitioned_pk); +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (2); +-- validated on one leaf partition only +CREATE TABLE fk_partitioned_fk_3 PARTITION OF fk_partitioned_fk FOR VALUES IN (3, 4) + PARTITION BY LIST (k); +CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES IN (3); +CREATE TABLE fk_partitioned_fk_3_2 PARTITION OF fk_partitioned_fk_3 FOR VALUES IN (4); +ALTER TABLE fk_partitioned_fk_3_1 VALIDATE CONSTRAINT fkey; + +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a TYPE bigint; + +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +-- ALTER COLUMN SET EXPRESSION should not changing foreign key constraints +ALTER TABLE fk_partitioned_fk DROP COLUMN a; +ALTER TABLE fk_partitioned_fk ADD COLUMN a int GENERATED ALWAYS AS (k) STORED; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fkey + FOREIGN KEY (a) REFERENCES fk_notpartitioned_pk NOT VALID; +ALTER TABLE fk_partitioned_fk_1 VALIDATE CONSTRAINT fkey; +ALTER TABLE fk_partitioned_fk_3_1 VALIDATE CONSTRAINT fkey; + +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a SET EXPRESSION AS (k * 1); + +SELECT conrelid::regclass, conname, coninhcount, convalidated, conparentid <> 0 as childconstr +FROM pg_constraint +WHERE contype = 'f' +AND conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk; + +-- When the referenced table is partitioned, a partition's own foreign key has +-- a child row for each referenced partition, on the same relation. Those are +-- not mistaken for the partition's copy of the parent's foreign key, even if +-- both foreign keys have the same name. +CREATE TABLE fk_partitioned_pk (a int PRIMARY KEY) PARTITION BY LIST (a); +CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES IN (1); +CREATE TABLE fk_partitioned_pk_2 PARTITION OF fk_partitioned_pk FOR VALUES IN (2); +CREATE TABLE fk_partitioned_fk (k int, a int) PARTITION BY LIST (k); +CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES IN (1); +ALTER TABLE fk_partitioned_fk_1 ADD CONSTRAINT fk + FOREIGN KEY (a) REFERENCES fk_partitioned_pk ON DELETE CASCADE; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk + FOREIGN KEY (a) REFERENCES fk_partitioned_pk NOT VALID; + +SELECT c.conrelid::regclass, c.conname, p.conname AS parent, c.convalidated +FROM pg_constraint c LEFT JOIN pg_constraint p ON p.oid = c.conparentid +WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY c.conrelid::regclass::text COLLATE "C", c.conname COLLATE "C"; + +ALTER TABLE fk_partitioned_fk ALTER COLUMN a TYPE bigint; + +SELECT c.conrelid::regclass, c.confrelid::regclass, c.conname, p.conname AS parent, c.convalidated +FROM pg_constraint c LEFT JOIN pg_constraint p ON p.oid = c.conparentid +WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'fk_partitioned_fk%' +ORDER BY c.conrelid::regclass::text COLLATE "C", c.conname COLLATE "C"; + +DROP TABLE fk_partitioned_fk, fk_partitioned_pk; + -- NOT VALID and NOT ENFORCED foreign key on a non-partitioned table -- referencing a partitioned table CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index 235bc28db81..0db5e71825e 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -421,6 +421,29 @@ ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok +ALTER TABLE gtestnn_parent ALTER COLUMN c DROP NOT NULL; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con1 NOT NULL c NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con2 CHECK (c > 0) NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con3 CHECK (c > 0) NOT ENFORCED; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con1; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con2; +ALTER TABLE gtestnn_child ALTER CONSTRAINT con3 ENFORCED; + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtestnn_parent ALTER COLUMN c SET EXPRESSION AS (nullif(f1, 4) + nullif(f2, 7)); + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE); INSERT INTO gtest22a VALUES (2); @@ -686,6 +709,28 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; \d gtest30_1_1 ROLLBACK; +ALTER TABLE gtest30 ADD CONSTRAINT con1 NOT NULL b NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con2 CHECK (b > 0) NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con3 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con1; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con2; +ALTER TABLE gtest30_1 ALTER CONSTRAINT con3 ENFORCED; + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 3); + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + -- test drop expression with subpartitions CREATE TABLE gtest_root (a int, b int, c int GENERATED ALWAYS AS (a + b) STORED) PARTITION BY LIST (a); CREATE TABLE gtest_node PARTITION OF gtest_root FOR VALUES IN (1) PARTITION BY LIST (b); diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index c4c4f3d5ef1..489c40cb9c0 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -429,6 +429,29 @@ ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) VIRTUAL; -- error ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok +ALTER TABLE gtestnn_parent ALTER COLUMN c DROP NOT NULL; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con1 NOT NULL c NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con2 CHECK (c > 0) NOT VALID; +ALTER TABLE gtestnn_parent ADD CONSTRAINT con3 CHECK (c > 0) NOT ENFORCED; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con1; +ALTER TABLE gtestnn_child VALIDATE CONSTRAINT con2; +ALTER TABLE gtestnn_child ALTER CONSTRAINT con3 ENFORCED; + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtestnn_parent ALTER COLUMN c SET EXPRESSION AS (nullif(f1, 4) + nullif(f2, 7)); + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtestnn_parent', 'gtestnn_child') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); --INSERT INTO gtest22a VALUES (2); @@ -696,6 +719,28 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error \d gtest30_1 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error +ALTER TABLE gtest30 ADD CONSTRAINT con1 NOT NULL b NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con2 CHECK (b > 0) NOT VALID; +ALTER TABLE gtest30 ADD CONSTRAINT con3 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con1; +ALTER TABLE gtest30_1 VALIDATE CONSTRAINT con2; +ALTER TABLE gtest30_1 ALTER CONSTRAINT con3 ENFORCED; + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + +-- ALTER COLUMN SET EXPRESSION should not changing not-null, check constraints +ALTER TABLE gtest30 ALTER COLUMN b SET EXPRESSION AS (a * 3); + +SELECT conrelid::regclass as relname, conname, conislocal, + conenforced, convalidated +FROM pg_constraint +WHERE conrelid::regclass::text in ('gtest30_1', 'gtest30') +ORDER BY conrelid::regclass::text COLLATE "C", conname COLLATE "C"; + -- composite type dependencies CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text); CREATE TABLE gtest31_2 (x int, y gtest31_1); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 072fca13c13..660327b9feb 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -622,6 +622,61 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); alter table p1_c1 inherit p1; drop table p1, p1_c1; +-- ALTER COLUMN SET DATA TYPE recreates an inherited constraint with the child's +-- own state, which can be ENFORCED or VALID while the parent's is not +create temp view constr_status_now as +select conrelid::regclass as relname, conname, conislocal, coninhcount, + conenforced, convalidated +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'pp', 'pp1', 'pp11'); + +create table p1(f1 int); +alter table p1 add constraint p1_nn not null f1 not valid; +alter table p1 add constraint p1_a_check check (f1 > 0) not valid; +alter table p1 add constraint p1_b_check check (f1 < 100) not enforced; +create table p1_c1() inherits (p1); +alter table p1_c1 validate constraint p1_nn; +alter table p1_c1 validate constraint p1_a_check; +alter table p1_c1 alter constraint p1_b_check enforced; +create table p1_c2(f1 int constraint p1_a_check check (f1 > 0)) inherits (p1); +select * from constr_status_now +order by relname::text collate "C", conname collate "C"; + +create temp table constr_status as table constr_status_now; + +alter table p1 alter column f1 type bigint; +-- ALTER COLUMN SET DATA TYPE should not change any constraint's status +(table constr_status except table constr_status_now) +union all +(table constr_status_now except table constr_status); +drop table constr_status; +drop table p1 cascade; + +-- same for partitions, and an ENFORCED NOT VALID one is not validated +create table pp(k int, f1 int) partition by list (k); +alter table pp add constraint pp_a_check check (f1 > 0) not valid; +alter table pp add constraint pp_b_check check (f1 < 100) not enforced; +create table pp1(k int, f1 int) partition by list (k); +create table pp11(k int, f1 int); +alter table pp11 add constraint pp_a_check check (f1 > 0); +alter table pp11 add constraint pp_b_check check (f1 < 100) not valid; +alter table pp1 attach partition pp11 for values in (1); +alter table pp1 add constraint pp_a_check check (f1 > 0) not valid; +alter table pp1 add constraint pp_b_check check (f1 < 100) not enforced; +alter table pp attach partition pp1 for values in (1); +select * from constr_status_now +order by relname::text collate "C", conname collate "C"; + +create temp table constr_status as table constr_status_now; +alter table pp alter column f1 type bigint; +-- ALTER COLUMN SET DATA TYPE should not change any constraint's status +(table constr_status except table constr_status_now) +union all +(table constr_status_now except table constr_status); +drop table constr_status; +drop table pp; +drop view constr_status_now; + -- -- Test DROP behavior of multiply-defined CHECK constraints -- diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index a7a8dbdd23f..598fde9ca4b 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -527,6 +527,7 @@ ConnectionWarningFilter ConsiderSplitContext Const ConstrCheck +ConstrInfo ConstrType Constraint ConstraintCategory -- 2.34.1
