hi. Rebase and polish the comments.
-- jian https://www.enterprisedb.com/
From 28f28d82fd2cfabbd9834648b51d54b88ee9742b Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 2 Mar 2026 04:15:04 +0800 Subject: [PATCH v6 1/1] ALTER DOMAIN ADD NOT NULL NOT VALID We already support NOT VALID for not-null table constraints. Now extends the same capability to domains by allowing domain's NOT NULL constraint to be marked as NOT VALID. This will affects other related ALTER DOMAIN command: ALTER DOMAIN ADD NOT NULL: If the domain already possesses a NOT VALID not-null constraint, this command raise an error, and fail. ALTER DOMAIN DROP NOT NULL: NOT VALID not-null domain constraint will be dropped. ALTER DOMAIN SET NOT NULL, similar to command ALTER TABLE SET NOT NULL, this will validate the existing NOT VALID not-null constraint and updates pg_constraint.convalidated to true. However if the domain already has a validated not-null constraint, adding a NOT VALID not-null constraint has no effect. \dD Output: The "Nullable" column in \dD will now display "not null not valid" for NOT VALID not-null doamin constraints. Author: jian he <[email protected]> Reviewed-by: Kirill Reshke <[email protected]> Reviewed-by: Quan Zongliang <[email protected]> discussion: https://postgr.es/m/CACJufxGcABLgmH951SJkkihK+FW8KR3=odbhxevcf9atqbu...@mail.gmail.com relate discussion: https://postgr.es/m/CACJufxE2oFcLmrqDrqJrH5k03fv+v9=+-PBs-mV5WsJ=31x...@mail.gmail.com commitfest entry: https://commitfest.postgresql.org/patch/5768 --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ref/alter_domain.sgml | 1 - src/backend/catalog/pg_constraint.c | 7 +- src/backend/commands/typecmds.c | 148 ++++++++++++++++++++++++--- src/backend/parser/gram.y | 8 +- src/bin/pg_dump/t/002_pg_dump.pl | 16 +++ src/bin/psql/describe.c | 5 +- src/test/regress/expected/domain.out | 37 +++++++ src/test/regress/sql/domain.sql | 24 +++++ 9 files changed, 219 insertions(+), 29 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index e7067c84ece..98fb2779f46 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9632,7 +9632,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <structfield>typnotnull</structfield> <type>bool</type> </para> <para> - <structfield>typnotnull</structfield> represents a not-null + <structfield>typnotnull</structfield> represents a (possibly invalid) not-null constraint on a type. Used for domains only. </para></entry> </row> diff --git a/doc/src/sgml/ref/alter_domain.sgml b/doc/src/sgml/ref/alter_domain.sgml index 74855172222..4807116eb05 100644 --- a/doc/src/sgml/ref/alter_domain.sgml +++ b/doc/src/sgml/ref/alter_domain.sgml @@ -92,7 +92,6 @@ ALTER DOMAIN <replaceable class="parameter">name</replaceable> valid using <command>ALTER DOMAIN ... VALIDATE CONSTRAINT</command>. Newly inserted or updated rows are always checked against all constraints, even those marked <literal>NOT VALID</literal>. - <literal>NOT VALID</literal> is only accepted for <literal>CHECK</literal> constraints. </para> </listitem> </varlistentry> diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index b12765ae691..09baf48dd57 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -651,7 +651,7 @@ findNotNullConstraint(Oid relid, const char *colname) } /* - * Find and return the pg_constraint tuple that implements a validated + * Find and return the pg_constraint tuple that implements a (possibly not valid) * not-null constraint for the given domain. */ HeapTuple @@ -675,13 +675,8 @@ findDomainNotNullConstraint(Oid typid) { Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); - /* - * We're looking for a NOTNULL constraint that's marked validated. - */ if (con->contype != CONSTRAINT_NOTNULL) continue; - if (!con->convalidated) - continue; /* Found it */ retval = heap_copytuple(conTup); diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 3dab6bb5a79..2137f68a70f 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -129,7 +129,7 @@ static Oid findRangeSubOpclass(List *opcname, Oid subtype); static Oid findRangeCanonicalFunction(List *procname, Oid typeOid); static Oid findRangeSubtypeDiffFunction(List *procname, Oid subtype); static void validateDomainCheckConstraint(Oid domainoid, const char *ccbin, LOCKMODE lockmode); -static void validateDomainNotNullConstraint(Oid domainoid); +static void validateDomainNotNullConstraint(Oid domainoid, LOCKMODE lockmode); static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode); static void checkEnumOwner(HeapTuple tup); static char *domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, @@ -2790,12 +2790,73 @@ AlterDomainNotNull(List *names, bool notNull) checkDomainOwner(tup); /* Is the domain already set to the desired constraint? */ - if (typTup->typnotnull == notNull) + if (!typTup->typnotnull && !notNull) { table_close(typrel, RowExclusiveLock); return address; } + if (typTup->typnotnull && notNull) + { + ScanKeyData key[1]; + SysScanDesc scan; + HeapTuple conTup; + + Relation pg_constraint = table_open(ConstraintRelationId, + AccessShareLock); + + ScanKeyInit(&key[0], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(domainoid)); + + scan = systable_beginscan(pg_constraint, ConstraintTypidIndexId, true, + NULL, 1, key); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + + if (con->contype != CONSTRAINT_NOTNULL) + continue; + + /* + * ALTER DOMAIN SET NOT NULL require validate the existing NOT + * VALID not-null domain constraint, while at it, set + * pg_constraint.convalidated to true. + */ + if (!con->convalidated) + { + HeapTuple copyTuple; + Form_pg_constraint copy_con; + + validateDomainNotNullConstraint(domainoid, ShareUpdateExclusiveLock); + + copyTuple = heap_copytuple(conTup); + copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple); + + copy_con->convalidated = true; + CatalogTupleUpdate(pg_constraint, ©Tuple->t_self, copyTuple); + + InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0); + + heap_freetuple(copyTuple); + } + + break; + } + systable_endscan(scan); + + table_close(pg_constraint, AccessShareLock); + table_close(typrel, RowExclusiveLock); + + /* + * Now the existing not-null domain constraint is valid, no need to + * add a new one. Exit now. + */ + return address; + } + if (notNull) { Constraint *constr; @@ -2809,7 +2870,7 @@ AlterDomainNotNull(List *names, bool notNull) typTup->typbasetype, typTup->typtypmod, constr, NameStr(typTup->typname), NULL); - validateDomainNotNullConstraint(domainoid); + validateDomainNotNullConstraint(domainoid, ShareLock); } else { @@ -3023,10 +3084,51 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, } else if (constr->contype == CONSTR_NOTNULL) { - /* Is the domain already set NOT NULL? */ + /* Is the domain already have a NOT NULL constraint? */ if (typTup->typnotnull) { + HeapTuple conTup; + ScanKeyData key[1]; + SysScanDesc scan; + + Relation pg_constraint = table_open(ConstraintRelationId, + AccessShareLock); + + ScanKeyInit(&key[0], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(domainoid)); + + scan = systable_beginscan(pg_constraint, + ConstraintTypidIndexId, + true, NULL, 1, key); + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + + if (con->contype != CONSTRAINT_NOTNULL) + continue; + + /* + * Cannot adding a new VALID NOT NULL constraint if the domain + * already has a NOT VALID one. However, adding a NOT VALID + * NOT NULL constraint when a VALID one exists is allowed. + */ + if (!con->convalidated && constr->initially_valid) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("incompatible NOT VALID constraint \"%s\" on domain \"%s\"", + NameStr(con->conname), + NameStr(typTup->typname)), + errhint("You might need to validate it using %s.", + "ALTER DOMAIN ... VALIDATE CONSTRAINT")); + break; + } + systable_endscan(scan); + + table_close(pg_constraint, AccessShareLock); table_close(typrel, RowExclusiveLock); + return address; } domainAddNotNullConstraint(domainoid, typTup->typnamespace, @@ -3034,7 +3136,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, constr, NameStr(typTup->typname), constrAddr); if (!constr->skip_validation) - validateDomainNotNullConstraint(domainoid); + validateDomainNotNullConstraint(domainoid, ShareLock); typTup->typnotnull = true; CatalogTupleUpdate(typrel, &tup->t_self, tup); @@ -3117,23 +3219,28 @@ AlterDomainValidateConstraint(List *names, const char *constrName) constrName, TypeNameToString(typename)))); con = (Form_pg_constraint) GETSTRUCT(tuple); - if (con->contype != CONSTRAINT_CHECK) + if (con->contype != CONSTRAINT_CHECK && con->contype != CONSTRAINT_NOTNULL) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("constraint \"%s\" of domain \"%s\" is not a check constraint", + errmsg("constraint \"%s\" of domain \"%s\" is not a check or not-null constraint", constrName, TypeNameToString(typename)))); if (!con->convalidated) { - val = SysCacheGetAttrNotNull(CONSTROID, tuple, Anum_pg_constraint_conbin); - conbin = TextDatumGetCString(val); + if (con->contype == CONSTRAINT_CHECK) + { + val = SysCacheGetAttrNotNull(CONSTROID, tuple, Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); - /* - * Locking related relations with ShareUpdateExclusiveLock is ok - * because not-yet-valid constraints are still enforced against - * concurrent inserts or updates. - */ - validateDomainCheckConstraint(domainoid, conbin, ShareUpdateExclusiveLock); + /* + * Locking related relations with ShareUpdateExclusiveLock is ok + * because not-yet-valid constraints are still enforced against + * concurrent inserts or updates. + */ + validateDomainCheckConstraint(domainoid, conbin, ShareUpdateExclusiveLock); + } + else + validateDomainNotNullConstraint(domainoid, ShareUpdateExclusiveLock); /* * Now update the catalog, while we have the door open. @@ -3162,9 +3269,16 @@ AlterDomainValidateConstraint(List *names, const char *constrName) /* * Verify that all columns currently using the domain are not null. + * + * This support validating the existing not-null constraint and adding a new + * not-null constraint on a domain. + * + * The lockmode is used for relations using the domain. It should be ShareLock + * when adding a new not-null to domain. It can be ShareUpdateExclusiveLock + * when validating the existing not-null constraint. */ static void -validateDomainNotNullConstraint(Oid domainoid) +validateDomainNotNullConstraint(Oid domainoid, LOCKMODE lockmode) { List *rels; ListCell *rt; @@ -3172,7 +3286,7 @@ validateDomainNotNullConstraint(Oid domainoid) /* Fetch relation list with attributes based on this domain */ /* ShareLock is sufficient to prevent concurrent data changes */ - rels = get_rels_with_domain(domainoid, ShareLock); + rels = get_rels_with_domain(domainoid, lockmode); foreach(rt, rels) { diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c567252acc4..bf1d3d1f584 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4512,11 +4512,13 @@ DomainConstraintElem: n->contype = CONSTR_NOTNULL; n->location = @1; n->keys = list_make1(makeString("value")); - /* no NOT VALID, NO INHERIT support */ + /* NO INHERIT is not supported */ processCASbits($3, @3, "NOT NULL", NULL, NULL, NULL, - NULL, NULL, yyscanner); - n->initially_valid = true; + &n->skip_validation, + NULL, yyscanner); + n->is_enforced = true; + n->initially_valid = !n->skip_validation; $$ = (Node *) n; } ; diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index f15bd06adcc..e17bf0ec99b 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -1026,6 +1026,22 @@ my %tests = ( }, }, + 'DOMAIN CONSTRAINT NOT NULL / NOT VALID' => { + create_sql => 'CREATE DOMAIN dump_test.test_domain_nn AS INT; + ALTER DOMAIN dump_test.test_domain_nn ADD CONSTRAINT nn NOT NULL NOT VALID;', + regexp => qr/^ + \QALTER DOMAIN dump_test.test_domain_nn\E \n^\s+ + \QADD CONSTRAINT nn NOT NULL NOT VALID;\E + /xm, + like => { + %full_runs, %dump_test_schema_runs, section_post_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + 'CONSTRAINT NOT NULL / NOT VALID (child1)' => { regexp => qr/^ \QCREATE TABLE dump_test.test_table_nn_chld1 (\E\n diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4352991e541..45f204e8818 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -4593,7 +4593,10 @@ listDomains(const char *pattern, bool verbose, bool showSystem) " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n" " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n" " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n" - " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n" + " CASE WHEN t.typnotnull THEN " + " (SELECT lower(pg_catalog.pg_get_constraintdef(r.oid, true)) FROM pg_catalog.pg_constraint r " + " WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_NOTNULL) ")" + " END as \"%s\",\n" " t.typdefault as \"%s\",\n" " pg_catalog.array_to_string(ARRAY(\n" " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_CHECK) " ORDER BY r.conname\n" diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 62a48a523a2..b6a3e5aa688 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -927,6 +927,43 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow; ERROR: column "stuff" of table "thethings" contains values that violate the new constraint UPDATE thethings SET stuff = 10; ALTER DOMAIN things VALIDATE CONSTRAINT meow; +ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; +ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- no-op +ALTER DOMAIN things DROP NOT NULL; +INSERT INTO thethings VALUES(NULL); +ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- ok +INSERT INTO thethings VALUES(NULL); -- error +ERROR: domain things does not allow null values +ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; -- error +ERROR: incompatible NOT VALID constraint "domain_nn" on domain "things" +HINT: You might need to validate it using ALTER DOMAIN ... VALIDATE CONSTRAINT. +ALTER DOMAIN things ADD NOT NULL; -- error +ERROR: incompatible NOT VALID constraint "domain_nn" on domain "things" +HINT: You might need to validate it using ALTER DOMAIN ... VALIDATE CONSTRAINT. +ALTER DOMAIN things SET NOT NULL; -- error +ERROR: column "stuff" of table "thethings" contains null values +ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- error +ERROR: column "stuff" of table "thethings" contains null values +ALTER DOMAIN things ADD CONSTRAINT domain_nn1 NOT NULL NOT VALID; -- no-op +\dD things + List of domains + Schema | Name | Type | Collation | Nullable | Default | Check +--------+--------+---------+-----------+--------------------+---------+-------------------- + public | things | integer | | not null not valid | | CHECK (VALUE < 11) +(1 row) + +SELECT conname, pg_get_constraintdef(oid) +FROM pg_constraint +WHERE contypid = 'things'::regtype AND contype = 'n'; + conname | pg_get_constraintdef +-----------+---------------------- + domain_nn | NOT NULL NOT VALID +(1 row) + +UPDATE thethings SET stuff = 10 WHERE stuff IS NULL; +ALTER DOMAIN things SET NOT NULL; -- ok +ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- ok +ALTER DOMAIN things DROP NOT NULL; -- Confirm ALTER DOMAIN with RULES. create table domtab (col1 integer); create domain dom as integer; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index b8f5a639712..9b367793450 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -537,6 +537,30 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow; UPDATE thethings SET stuff = 10; ALTER DOMAIN things VALIDATE CONSTRAINT meow; +ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; +ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- no-op +ALTER DOMAIN things DROP NOT NULL; + +INSERT INTO thethings VALUES(NULL); +ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- ok + +INSERT INTO thethings VALUES(NULL); -- error +ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; -- error +ALTER DOMAIN things ADD NOT NULL; -- error +ALTER DOMAIN things SET NOT NULL; -- error +ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- error +ALTER DOMAIN things ADD CONSTRAINT domain_nn1 NOT NULL NOT VALID; -- no-op + +\dD things +SELECT conname, pg_get_constraintdef(oid) +FROM pg_constraint +WHERE contypid = 'things'::regtype AND contype = 'n'; + +UPDATE thethings SET stuff = 10 WHERE stuff IS NULL; +ALTER DOMAIN things SET NOT NULL; -- ok +ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- ok +ALTER DOMAIN things DROP NOT NULL; + -- Confirm ALTER DOMAIN with RULES. create table domtab (col1 integer); create domain dom as integer; -- 2.34.1
