On 18.01.24 07:53, Peter Eisentraut wrote:
On 17.01.24 13:15, vignesh C wrote:
One of the test has failed in CFBot at [1] with:
diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/domain.out
/tmp/cirrus-ci-build/src/test/regress/results/domain.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/domain.out
2024-01-14 15:40:01.793434601 +0000
+++ /tmp/cirrus-ci-build/src/test/regress/results/domain.out
2024-01-14 15:42:23.013332625 +0000
@@ -1271,11 +1271,4 @@
FROM information_schema.domain_constraints
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
ORDER BY constraint_name;
- constraint_catalog | constraint_schema | constraint_name |
check_clause
---------------------+-------------------+------------------+-------------------
- regression | public | con_check | (VALUE > 0)
- regression | public | meow | (VALUE <
11)
- regression | public | pos_int_check | (VALUE > 0)
- regression | public | pos_int_not_null | VALUE IS
NOT NULL
-(4 rows)
-
+ERROR: could not open relation with OID 36379
[1] - https://cirrus-ci.com/task/4536440638406656
[2] -
https://api.cirrus-ci.com/v1/artifact/task/4536440638406656/log/src/test/regress/regression.diffs
Interesting. I couldn't reproduce this locally, even across different
operating systems. The cfbot failures appear to be sporadic, but also
happening across multiple systems, so it's clearly not just a local
environment failure. Can anyone else perhaps reproduce this locally?
This patch set needed a rebase, so here it is.
About the sporadic test failure above, I think that is an existing issue
that is just now exposed through some test timing changes. The
pg_get_expr() function used in information_schema.check_constraints has
no locking against concurrent drops of tables. I think in this
particular case, the tests "domain" and "alter_table" are prone to this
conflict. If I move "domain" to a separate test group, the issue goes
away. I'll start a separate discussion about this issue.
From cd48c8eb8aa5958020e899b8406cbd7866805db5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 23 Nov 2023 07:35:32 +0100
Subject: [PATCH v3 1/2] Add tests for domain-related information schema views
Discussion:
https://www.postgresql.org/message-id/flat/9ec24d7b-633d-463a-84c6-7acff769c9e8%40eisentraut.org
---
src/test/regress/expected/domain.out | 47 ++++++++++++++++++++++++++++
src/test/regress/sql/domain.sql | 24 ++++++++++++++
2 files changed, 71 insertions(+)
diff --git a/src/test/regress/expected/domain.out
b/src/test/regress/expected/domain.out
index 6d94e84414a..e70aebd70c0 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1207,3 +1207,50 @@ create domain testdomain1 as int constraint unsigned
check (value > 0);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;
+--
+-- Information schema
+--
+SELECT * FROM information_schema.column_domain_usage
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY domain_name;
+ domain_catalog | domain_schema | domain_name | table_catalog | table_schema |
table_name | column_name
+----------------+---------------+-------------+---------------+--------------+------------+-------------
+ regression | public | con | regression | public |
domcontest | col1
+ regression | public | dom | regression | public |
domview | col1
+ regression | public | things | regression | public |
thethings | stuff
+(3 rows)
+
+SELECT * FROM information_schema.domain_constraints
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY constraint_name;
+ constraint_catalog | constraint_schema | constraint_name | domain_catalog |
domain_schema | domain_name | is_deferrable | initially_deferred
+--------------------+-------------------+-----------------+----------------+---------------+-------------+---------------+--------------------
+ regression | public | con_check | regression |
public | con | NO | NO
+ regression | public | meow | regression |
public | things | NO | NO
+ regression | public | pos_int_check | regression |
public | pos_int | NO | NO
+(3 rows)
+
+SELECT * FROM information_schema.domains
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY domain_name;
+ domain_catalog | domain_schema | domain_name | data_type |
character_maximum_length | character_octet_length | character_set_catalog |
character_set_schema | character_set_name | collation_catalog |
collation_schema | collation_name | numeric_precision | numeric_precision_radix
| numeric_scale | datetime_precision | interval_type | interval_precision |
domain_default | udt_catalog | udt_schema | udt_name | scope_catalog |
scope_schema | scope_name | maximum_cardinality | dtd_identifier
+----------------+---------------+-------------+-----------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+----------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------
+ regression | public | con | integer |
| | | |
| | | |
32 | 2 | 0 | |
| | | regression | pg_catalog
| int4 | | | | | 1
+ regression | public | dom | integer |
| | | |
| | | |
32 | 2 | 0 | |
| | | regression | pg_catalog
| int4 | | | | | 1
+ regression | public | pos_int | integer |
| | | |
| | | |
32 | 2 | 0 | |
| | | regression | pg_catalog
| int4 | | | | | 1
+ regression | public | things | integer |
| | | |
| | | |
32 | 2 | 0 | |
| | | regression | pg_catalog
| int4 | | | | | 1
+(4 rows)
+
+SELECT * FROM information_schema.check_constraints
+ WHERE (constraint_schema, constraint_name)
+ IN (SELECT constraint_schema, constraint_name
+ FROM information_schema.domain_constraints
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
+ ORDER BY constraint_name;
+ constraint_catalog | constraint_schema | constraint_name | check_clause
+--------------------+-------------------+-----------------+--------------
+ regression | public | con_check | (VALUE > 0)
+ regression | public | meow | (VALUE < 11)
+ regression | public | pos_int_check | (VALUE > 0)
+(3 rows)
+
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 745f5d5fd2b..813048c19f5 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -809,3 +809,27 @@ CREATE TABLE thethings (stuff things);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;
+
+
+--
+-- Information schema
+--
+
+SELECT * FROM information_schema.column_domain_usage
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY domain_name;
+
+SELECT * FROM information_schema.domain_constraints
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY constraint_name;
+
+SELECT * FROM information_schema.domains
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
+ ORDER BY domain_name;
+
+SELECT * FROM information_schema.check_constraints
+ WHERE (constraint_schema, constraint_name)
+ IN (SELECT constraint_schema, constraint_name
+ FROM information_schema.domain_constraints
+ WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
+ ORDER BY constraint_name;
base-commit: b83033c3cff556d520281aaec399e47b4f11edbe
--
2.43.0
From d34881a605d43241686816aa00603aa1b4a55179 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 6 Feb 2024 14:55:54 +0100
Subject: [PATCH v3 2/2] Catalog domain not-null constraints
This applies the explicit catalog representation of not-null
constraints introduced by b0e96f3119 for table constraints also to
domain not-null constraints.
TODO: catversion
Discussion:
https://www.postgresql.org/message-id/flat/9ec24d7b-633d-463a-84c6-7acff769c9e8%40eisentraut.org
---
src/backend/catalog/information_schema.sql | 2 +-
src/backend/catalog/pg_constraint.c | 40 +++
src/backend/commands/typecmds.c | 320 +++++++++++++++------
src/backend/utils/adt/ruleutils.c | 22 +-
src/backend/utils/cache/typcache.c | 2 +-
src/bin/pg_dump/pg_dump.c | 2 +-
src/include/catalog/pg_constraint.h | 1 +
src/test/regress/expected/domain.out | 49 +++-
src/test/regress/sql/domain.sql | 26 ++
9 files changed, 351 insertions(+), 113 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index c402ae72741..76c78c0d184 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -445,7 +445,7 @@ CREATE VIEW check_constraints AS
SELECT current_database()::information_schema.sql_identifier AS
constraint_catalog,
rs.nspname::information_schema.sql_identifier AS constraint_schema,
con.conname::information_schema.sql_identifier AS constraint_name,
- pg_catalog.format('%s IS NOT NULL',
at.attname)::information_schema.character_data AS check_clause
+ pg_catalog.format('%s IS NOT NULL', coalesce(at.attname,
'VALUE'))::information_schema.character_data AS check_clause
FROM pg_constraint con
LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace
LEFT JOIN pg_class c ON c.oid = con.conrelid
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index 0a95608179d..59ca40b7c23 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -631,6 +631,46 @@ findNotNullConstraint(Oid relid, const char *colname)
return findNotNullConstraintAttnum(relid, attnum);
}
+HeapTuple
+findDomainNotNullConstraint(Oid typid)
+{
+ Relation pg_constraint;
+ HeapTuple conTup,
+ retval = NULL;
+ SysScanDesc scan;
+ ScanKeyData key;
+
+ pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+ ScanKeyInit(&key,
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(typid));
+ scan = systable_beginscan(pg_constraint,
ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &key);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+ {
+ 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);
+ break;
+ }
+
+ systable_endscan(scan);
+ table_close(pg_constraint, AccessShareLock);
+
+ return retval;
+}
+
/*
* Given a pg_constraint tuple for a not-null constraint, return the column
* number it is for.
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index a400fb39f67..42ee58aab70 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -126,15 +126,19 @@ static Oid findTypeSubscriptingFunction(List
*procname, Oid typeOid);
static Oid findRangeSubOpclass(List *opcname, Oid subtype);
static Oid findRangeCanonicalFunction(List *procname, Oid typeOid);
static Oid findRangeSubtypeDiffFunction(List *procname, Oid subtype);
-static void validateDomainConstraint(Oid domainoid, char *ccbin);
+static void validateDomainCheckConstraint(Oid domainoid, char *ccbin);
+static void validateDomainNotNullConstraint(Oid domainoid);
static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode);
static void checkEnumOwner(HeapTuple tup);
-static char *domainAddConstraint(Oid domainOid, Oid domainNamespace,
- Oid
baseTypeOid,
- int typMod,
Constraint *constr,
- const char
*domainName, ObjectAddress *constrAddr);
+static char *domainAddCheckConstraint(Oid domainOid, Oid domainNamespace,
+ Oid
baseTypeOid,
+ int
typMod, Constraint *constr,
+ const
char *domainName, ObjectAddress *constrAddr);
static Node *replace_domain_constraint_value(ParseState *pstate,
ColumnRef *cref);
+static void domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid
baseTypeOid,
+ int
typMod, Constraint *constr,
+
const char *domainName, ObjectAddress *constrAddr);
static void AlterTypeRecurse(Oid typeOid, bool isImplicitArray,
HeapTuple tup,
Relation catalog,
AlterTypeRecurseParams
*atparams);
@@ -1105,9 +1109,15 @@ DefineDomain(CreateDomainStmt *stmt)
switch (constr->contype)
{
case CONSTR_CHECK:
- domainAddConstraint(address.objectId,
domainNamespace,
-
basetypeoid, basetypeMod,
- constr,
domainName, NULL);
+ domainAddCheckConstraint(address.objectId,
domainNamespace,
+
basetypeoid, basetypeMod,
+
constr, domainName, NULL);
+ break;
+
+ case CONSTR_NOTNULL:
+ domainAddNotNullConstraint(address.objectId,
domainNamespace,
+
basetypeoid, basetypeMod,
+
constr, domainName, NULL);
break;
/* Other constraint types were fully processed
above */
@@ -2723,66 +2733,32 @@ AlterDomainNotNull(List *names, bool notNull)
return address;
}
- /* Adding a NOT NULL constraint requires checking existing columns */
if (notNull)
{
- List *rels;
- ListCell *rt;
+ Constraint *constr;
- /* Fetch relation list with attributes based on this domain */
- /* ShareLock is sufficient to prevent concurrent data changes */
+ constr = makeNode(Constraint);
+ constr->contype = CONSTR_NOTNULL;
+ constr->initially_valid = true;
+ constr->location = -1;
- rels = get_rels_with_domain(domainoid, ShareLock);
+ domainAddNotNullConstraint(domainoid, typTup->typnamespace,
+
typTup->typbasetype, typTup->typtypmod,
+ constr,
NameStr(typTup->typname), NULL);
- foreach(rt, rels)
- {
- RelToCheck *rtc = (RelToCheck *) lfirst(rt);
- Relation testrel = rtc->rel;
- TupleDesc tupdesc = RelationGetDescr(testrel);
- TupleTableSlot *slot;
- TableScanDesc scan;
- Snapshot snapshot;
-
- /* Scan all tuples in this relation */
- snapshot = RegisterSnapshot(GetLatestSnapshot());
- scan = table_beginscan(testrel, snapshot, 0, NULL);
- slot = table_slot_create(testrel, NULL);
- while (table_scan_getnextslot(scan,
ForwardScanDirection, slot))
- {
- int i;
+ validateDomainNotNullConstraint(domainoid);
+ }
+ else
+ {
+ HeapTuple conTup;
+ ObjectAddress conobj;
- /* Test attributes that are of the domain */
- for (i = 0; i < rtc->natts; i++)
- {
- int attnum =
rtc->atts[i];
- Form_pg_attribute attr =
TupleDescAttr(tupdesc, attnum - 1);
+ conTup = findDomainNotNullConstraint(domainoid);
+ if (conTup == NULL)
+ elog(ERROR, "could not find not-null constraint on
domain \"%s\"", NameStr(typTup->typname));
- if (slot_attisnull(slot, attnum))
- {
- /*
- * In principle the auxiliary
information for this
- * error should be
errdatatype(), but errtablecol()
- * seems considerably more
useful in practice. Since
- * this code only executes in
an ALTER DOMAIN command,
- * the client should already
know which domain is in
- * question.
- */
- ereport(ERROR,
-
(errcode(ERRCODE_NOT_NULL_VIOLATION),
- errmsg("column
\"%s\" of table \"%s\" contains null values",
-
NameStr(attr->attname),
-
RelationGetRelationName(testrel)),
-
errtablecol(testrel, attnum)));
- }
- }
- }
- ExecDropSingleTupleTableSlot(slot);
- table_endscan(scan);
- UnregisterSnapshot(snapshot);
-
- /* Close each rel after processing, but keep lock */
- table_close(testrel, NoLock);
- }
+ ObjectAddressSet(conobj, ConstraintRelationId,
((Form_pg_constraint) GETSTRUCT(conTup))->oid);
+ performDeletion(&conobj, DROP_RESTRICT, 0);
}
/*
@@ -2863,10 +2839,17 @@ AlterDomainDropConstraint(List *names, const char
*constrName,
/* There can be at most one matching row */
if ((contup = systable_getnext(conscan)) != NULL)
{
+ Form_pg_constraint construct = (Form_pg_constraint)
GETSTRUCT(contup);
ObjectAddress conobj;
+ if (construct->contype == CONSTRAINT_NOTNULL)
+ {
+ ((Form_pg_type) GETSTRUCT(tup))->typnotnull = false;
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+ }
+
conobj.classId = ConstraintRelationId;
- conobj.objectId = ((Form_pg_constraint) GETSTRUCT(contup))->oid;
+ conobj.objectId = construct->oid;
conobj.objectSubId = 0;
performDeletion(&conobj, behavior, 0);
@@ -2947,6 +2930,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
switch (constr->contype)
{
case CONSTR_CHECK:
+ case CONSTR_NOTNULL:
/* processed below */
break;
@@ -2989,29 +2973,46 @@ AlterDomainAddConstraint(List *names, Node
*newConstraint,
break;
}
- /*
- * Since all other constraint types throw errors, this must be a check
- * constraint. First, process the constraint expression and add an
entry
- * to pg_constraint.
- */
+ if (constr->contype == CONSTR_CHECK)
+ {
+ /*
+ * First, process the constraint expression and add an entry to
+ * pg_constraint.
+ */
- ccbin = domainAddConstraint(domainoid, typTup->typnamespace,
-
typTup->typbasetype, typTup->typtypmod,
- constr,
NameStr(typTup->typname), constrAddr);
+ ccbin = domainAddCheckConstraint(domainoid,
typTup->typnamespace,
+
typTup->typbasetype, typTup->typtypmod,
+
constr, NameStr(typTup->typname), constrAddr);
- /*
- * If requested to validate the constraint, test all values stored in
the
- * attributes based on the domain the constraint is being added to.
- */
- if (!constr->skip_validation)
- validateDomainConstraint(domainoid, ccbin);
- /*
- * We must send out an sinval message for the domain, to ensure that any
- * dependent plans get rebuilt. Since this command doesn't change the
- * domain's pg_type row, that won't happen automatically; do it
manually.
- */
- CacheInvalidateHeapTuple(typrel, tup, NULL);
+ /*
+ * If requested to validate the constraint, test all values
stored in
+ * the attributes based on the domain the constraint is being
added
+ * to.
+ */
+ if (!constr->skip_validation)
+ validateDomainCheckConstraint(domainoid, ccbin);
+
+ /*
+ * We must send out an sinval message for the domain, to ensure
that
+ * any dependent plans get rebuilt. Since this command doesn't
change
+ * the domain's pg_type row, that won't happen automatically;
do it
+ * manually.
+ */
+ CacheInvalidateHeapTuple(typrel, tup, NULL);
+ }
+ else if (constr->contype == CONSTR_NOTNULL)
+ {
+ domainAddNotNullConstraint(domainoid, typTup->typnamespace,
+
typTup->typbasetype, typTup->typtypmod,
+ constr,
NameStr(typTup->typname), constrAddr);
+
+ if (!constr->skip_validation)
+ validateDomainNotNullConstraint(domainoid);
+
+ typTup->typnotnull = true;
+ CatalogTupleUpdate(typrel, &tup->t_self, tup);
+ }
ObjectAddressSet(address, TypeRelationId, domainoid);
@@ -3096,7 +3097,7 @@ AlterDomainValidateConstraint(List *names, const char
*constrName)
val = SysCacheGetAttrNotNull(CONSTROID, tuple,
Anum_pg_constraint_conbin);
conbin = TextDatumGetCString(val);
- validateDomainConstraint(domainoid, conbin);
+ validateDomainCheckConstraint(domainoid, conbin);
/*
* Now update the catalog, while we have the door open.
@@ -3123,7 +3124,68 @@ AlterDomainValidateConstraint(List *names, const char
*constrName)
}
static void
-validateDomainConstraint(Oid domainoid, char *ccbin)
+validateDomainNotNullConstraint(Oid domainoid)
+{
+ List *rels;
+ ListCell *rt;
+
+ /* Fetch relation list with attributes based on this domain */
+ /* ShareLock is sufficient to prevent concurrent data changes */
+
+ rels = get_rels_with_domain(domainoid, ShareLock);
+
+ foreach(rt, rels)
+ {
+ RelToCheck *rtc = (RelToCheck *) lfirst(rt);
+ Relation testrel = rtc->rel;
+ TupleDesc tupdesc = RelationGetDescr(testrel);
+ TupleTableSlot *slot;
+ TableScanDesc scan;
+ Snapshot snapshot;
+
+ /* Scan all tuples in this relation */
+ snapshot = RegisterSnapshot(GetLatestSnapshot());
+ scan = table_beginscan(testrel, snapshot, 0, NULL);
+ slot = table_slot_create(testrel, NULL);
+ while (table_scan_getnextslot(scan, ForwardScanDirection, slot))
+ {
+ int i;
+
+ /* Test attributes that are of the domain */
+ for (i = 0; i < rtc->natts; i++)
+ {
+ int attnum = rtc->atts[i];
+ Form_pg_attribute attr = TupleDescAttr(tupdesc,
attnum - 1);
+
+ if (slot_attisnull(slot, attnum))
+ {
+ /*
+ * In principle the auxiliary
information for this error
+ * should be errdatatype(), but
errtablecol() seems
+ * considerably more useful in
practice. Since this code
+ * only executes in an ALTER DOMAIN
command, the client
+ * should already know which domain is
in question.
+ */
+ ereport(ERROR,
+
(errcode(ERRCODE_NOT_NULL_VIOLATION),
+ errmsg("column \"%s\"
of table \"%s\" contains null values",
+
NameStr(attr->attname),
+
RelationGetRelationName(testrel)),
+ errtablecol(testrel,
attnum)));
+ }
+ }
+ }
+ ExecDropSingleTupleTableSlot(slot);
+ table_endscan(scan);
+ UnregisterSnapshot(snapshot);
+
+ /* Close each rel after processing, but keep lock */
+ table_close(testrel, NoLock);
+ }
+}
+
+static void
+validateDomainCheckConstraint(Oid domainoid, char *ccbin)
{
Expr *expr = (Expr *) stringToNode(ccbin);
List *rels;
@@ -3429,12 +3491,12 @@ checkDomainOwner(HeapTuple tup)
}
/*
- * domainAddConstraint - code shared between CREATE and ALTER DOMAIN
+ * domainAddCheckConstraint - code shared between CREATE and ALTER DOMAIN
*/
static char *
-domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
- int typMod, Constraint *constr,
- const char *domainName, ObjectAddress
*constrAddr)
+domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
+ int typMod, Constraint *constr,
+ const char *domainName,
ObjectAddress *constrAddr)
{
Node *expr;
char *ccbin;
@@ -3442,6 +3504,8 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
CoerceToDomainValue *domVal;
Oid ccoid;
+ Assert(constr->contype == CONSTR_CHECK);
+
/*
* Assign or validate constraint name
*/
@@ -3562,9 +3626,10 @@ replace_domain_constraint_value(ParseState *pstate,
ColumnRef *cref)
{
/*
* Check for a reference to "value", and if that's what it is, replace
- * with a CoerceToDomainValue as prepared for us by domainAddConstraint.
- * (We handle VALUE as a name, not a keyword, to avoid breaking a lot of
- * applications that have used VALUE as a column name in the past.)
+ * with a CoerceToDomainValue as prepared for us by
+ * domainAddCheckConstraint. (We handle VALUE as a name, not a keyword,
to
+ * avoid breaking a lot of applications that have used VALUE as a column
+ * name in the past.)
*/
if (list_length(cref->fields) == 1)
{
@@ -3584,6 +3649,79 @@ replace_domain_constraint_value(ParseState *pstate,
ColumnRef *cref)
return NULL;
}
+/*
+ * domainAddNotNullConstraint - code shared between CREATE and ALTER DOMAIN
+ */
+static void
+domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
+ int typMod, Constraint
*constr,
+ const char *domainName,
ObjectAddress *constrAddr)
+{
+ Oid ccoid;
+
+ Assert(constr->contype == CONSTR_NOTNULL);
+
+ /*
+ * Assign or validate constraint name
+ */
+ if (constr->conname)
+ {
+ if (ConstraintNameIsUsed(CONSTRAINT_DOMAIN,
+ domainOid,
+
constr->conname))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for domain
\"%s\" already exists",
+ constr->conname,
domainName)));
+ }
+ else
+ constr->conname = ChooseConstraintName(domainName,
+
NULL,
+
"not_null",
+
domainNamespace,
+
NIL);
+
+ /*
+ * Store the constraint in pg_constraint
+ */
+ ccoid =
+ CreateConstraintEntry(constr->conname, /* Constraint Name */
+ domainNamespace,
/* namespace */
+ CONSTRAINT_NOTNULL,
/* Constraint Type */
+ false, /* Is
Deferrable */
+ false, /* Is
Deferred */
+
!constr->skip_validation, /* Is Validated */
+ InvalidOid, /* no
parent constraint */
+ InvalidOid, /* not
a relation constraint */
+ NULL,
+ 0,
+ 0,
+ domainOid, /*
domain constraint */
+ InvalidOid, /* no
associated index */
+ InvalidOid, /*
Foreign key fields */
+ NULL,
+ NULL,
+ NULL,
+ NULL,
+ 0,
+ ' ',
+ ' ',
+ NULL,
+ 0,
+ ' ',
+ NULL, /* not an
exclusion constraint */
+ NULL,
+ NULL,
+ true, /* is local */
+ 0, /* inhcount */
+ false, /*
connoinherit */
+ false, /*
conwithoutoverlaps */
+ false); /*
is_internal */
+
+ if (constrAddr)
+ ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid);
+}
+
/*
* Execute ALTER TYPE RENAME
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index b625f471a84..e57bbcdbc05 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2494,15 +2494,23 @@ pg_get_constraintdef_worker(Oid constraintId, bool
fullCommand,
}
case CONSTRAINT_NOTNULL:
{
- AttrNumber attnum;
+ if (conForm->conrelid)
+ {
+ AttrNumber attnum;
- attnum = extractNotNullColumn(tup);
+ attnum = extractNotNullColumn(tup);
- appendStringInfo(&buf, "NOT NULL %s",
-
quote_identifier(get_attname(conForm->conrelid,
-
attnum, false)));
- if (((Form_pg_constraint)
GETSTRUCT(tup))->connoinherit)
- appendStringInfoString(&buf, " NO
INHERIT");
+ appendStringInfo(&buf, "NOT NULL %s",
+
quote_identifier(get_attname(conForm->conrelid,
+
attnum, false)));
+ if (((Form_pg_constraint)
GETSTRUCT(tup))->connoinherit)
+ appendStringInfoString(&buf, "
NO INHERIT");
+ }
+ else if (conForm->contypid)
+ {
+ /* conkey is null for domain not-null
constraints */
+ appendStringInfoString(&buf, "NOT NULL
VALUE");
+ }
break;
}
diff --git a/src/backend/utils/cache/typcache.c
b/src/backend/utils/cache/typcache.c
index 84fc83cb11f..2d689242b65 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1069,7 +1069,7 @@ load_domaintype_info(TypeCacheEntry *typentry)
Expr *check_expr;
DomainConstraintState *r;
- /* Ignore non-CHECK constraints (presently, shouldn't
be any) */
+ /* Ignore non-CHECK constraints */
if (c->contype != CONSTRAINT_CHECK)
continue;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 348748bae53..6261d3e0fb7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7831,7 +7831,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
"convalidated "
"FROM
pg_catalog.pg_constraint "
- "WHERE contypid = $1 "
+ "WHERE contypid = $1
AND contype = 'c' "
"ORDER BY conname");
ExecuteSqlStatement(fout, query->data);
diff --git a/src/include/catalog/pg_constraint.h
b/src/include/catalog/pg_constraint.h
index 01e6bc21cd1..4e738b50b2e 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -257,6 +257,7 @@ extern char *ChooseConstraintName(const char *name1, const
char *name2,
extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum);
extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
+extern HeapTuple findDomainNotNullConstraint(Oid typid);
extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
extern bool AdjustNotNullInheritance1(Oid relid, AttrNumber attnum, int count,
bool
is_no_inherit);
diff --git a/src/test/regress/expected/domain.out
b/src/test/regress/expected/domain.out
index e70aebd70c0..799b268ac76 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -798,6 +798,29 @@ alter domain con drop constraint nonexistent;
ERROR: constraint "nonexistent" of domain "con" does not exist
alter domain con drop constraint if exists nonexistent;
NOTICE: constraint "nonexistent" of domain "con" does not exist, skipping
+-- not-null constraints
+create domain connotnull integer;
+create table domconnotnulltest
+( col1 connotnull
+, col2 connotnull
+);
+insert into domconnotnulltest default values;
+alter domain connotnull add not null value; -- fails
+ERROR: column "col1" of table "domconnotnulltest" contains null values
+update domconnotnulltest set col1 = 5;
+alter domain connotnull add not null value; -- fails
+ERROR: column "col2" of table "domconnotnulltest" contains null values
+update domconnotnulltest set col2 = 6;
+alter domain connotnull add constraint constr1 not null value;
+update domconnotnulltest set col1 = null; -- fails
+ERROR: domain connotnull does not allow null values
+alter domain connotnull drop constraint constr1;
+update domconnotnulltest set col1 = null;
+drop domain connotnull cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to column col2 of table domconnotnulltest
+drop cascades to column col1 of table domconnotnulltest
+drop table domconnotnulltest;
-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
create domain things AS INT;
CREATE TABLE thethings (stuff things);
@@ -1223,12 +1246,13 @@ SELECT * FROM information_schema.column_domain_usage
SELECT * FROM information_schema.domain_constraints
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
ORDER BY constraint_name;
- constraint_catalog | constraint_schema | constraint_name | domain_catalog |
domain_schema | domain_name | is_deferrable | initially_deferred
---------------------+-------------------+-----------------+----------------+---------------+-------------+---------------+--------------------
- regression | public | con_check | regression |
public | con | NO | NO
- regression | public | meow | regression |
public | things | NO | NO
- regression | public | pos_int_check | regression |
public | pos_int | NO | NO
-(3 rows)
+ constraint_catalog | constraint_schema | constraint_name | domain_catalog |
domain_schema | domain_name | is_deferrable | initially_deferred
+--------------------+-------------------+------------------+----------------+---------------+-------------+---------------+--------------------
+ regression | public | con_check | regression |
public | con | NO | NO
+ regression | public | meow | regression |
public | things | NO | NO
+ regression | public | pos_int_check | regression |
public | pos_int | NO | NO
+ regression | public | pos_int_not_null | regression |
public | pos_int | NO | NO
+(4 rows)
SELECT * FROM information_schema.domains
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things')
@@ -1247,10 +1271,11 @@ SELECT * FROM information_schema.check_constraints
FROM information_schema.domain_constraints
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
ORDER BY constraint_name;
- constraint_catalog | constraint_schema | constraint_name | check_clause
---------------------+-------------------+-----------------+--------------
- regression | public | con_check | (VALUE > 0)
- regression | public | meow | (VALUE < 11)
- regression | public | pos_int_check | (VALUE > 0)
-(3 rows)
+ constraint_catalog | constraint_schema | constraint_name | check_clause
+--------------------+-------------------+------------------+-------------------
+ regression | public | con_check | (VALUE > 0)
+ regression | public | meow | (VALUE < 11)
+ regression | public | pos_int_check | (VALUE > 0)
+ regression | public | pos_int_not_null | VALUE IS NOT NULL
+(4 rows)
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 813048c19f5..0e71f040040 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -469,6 +469,32 @@
alter domain con drop constraint nonexistent;
alter domain con drop constraint if exists nonexistent;
+-- not-null constraints
+create domain connotnull integer;
+create table domconnotnulltest
+( col1 connotnull
+, col2 connotnull
+);
+
+insert into domconnotnulltest default values;
+alter domain connotnull add not null value; -- fails
+
+update domconnotnulltest set col1 = 5;
+alter domain connotnull add not null value; -- fails
+
+update domconnotnulltest set col2 = 6;
+
+alter domain connotnull add constraint constr1 not null value;
+
+update domconnotnulltest set col1 = null; -- fails
+
+alter domain connotnull drop constraint constr1;
+
+update domconnotnulltest set col1 = null;
+
+drop domain connotnull cascade;
+drop table domconnotnulltest;
+
-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
create domain things AS INT;
CREATE TABLE thethings (stuff things);
--
2.43.0
