hi.
https://coverage.postgresql.org/src/backend/commands/typecmds.c.gcov.html
show DefineDomain has poor coverage tests.
so I added a more extensive regression test.
also
create domain d_fail as int4 constraint cc GENERATED ALWAYS AS (2) STORED;
ERROR: unrecognized constraint subtype: 4
This kind of error message is not helpful. so I changed it to
ERROR: generated columns are not supported on domains
create domain d_int as int4;
ALTER DOMAIN d_int ADD constraint cc check(value > 1) no inherit not valid;
should fail.
so I made it fail.
typedef struct AlterDomainStmt
{
NodeTag type;
char subtype;
List *typeName; /* domain to work on */
char *name; /* column or constraint name to act on */
Node *def; /* definition of default or constraint */
DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
bool missing_ok; /* skip error if missing? */
} AlterDomainStmt;
we only have one `Node *def; ` in AlterDomainStmt
unlike CreateDomainStmt, have `List *constraints;`
so I guess we have to allow the following ALTER DOMAIN queries.
even though the corresponding CREATE DOMAIN stmt would fail.
create domain d_int as int4;
ALTER DOMAIN d_int ADD constraint cc1 check(value > 1) INITIALLY IMMEDIATE; --ok
ALTER DOMAIN d_int ADD constraint cc2 check(value > 1) NOT DEFERRABLE; --ok
ALTER DOMAIN d_int ADD constraint cc3 check(value > 1) NOT DEFERRABLE
not valid; --ok
ALTER DOMAIN d_int ADD constraint cc4 check(value > 1) INITIALLY
IMMEDIATE not valid; --ok
looking at pg_constraint, for the above queries, column {condeferrable
| condeferred | convalidated} all set properly.
i didn't found a way to trigger
errmsg("exclusion constraints not possible for domains")));
From 35d9ce7318b259281d1c8cf347dc0594e9ac62ba Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 11 Nov 2024 16:49:34 +0800
Subject: [PATCH v1 1/1] add more CREATE|ALTER DOMAIN tests
also AlterDomainAddConstraint, DefineDomain refactoring
---
src/backend/commands/typecmds.c | 20 ++++++-
src/test/regress/expected/domain.out | 84 ++++++++++++++++++++++++++++
src/test/regress/sql/domain.sql | 37 ++++++++++++
3 files changed, 140 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 859e2191f0..8c602ca63e 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1011,6 +1011,16 @@ DefineDomain(CreateDomainStmt *stmt)
errmsg("specifying constraint deferrability not supported for domains")));
break;
+ case CONSTR_IDENTITY:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("identity columns are not supported on domains")));
+ break;
+ case CONSTR_GENERATED:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("generated columns are not supported on domains")));
+ break;
default:
elog(ERROR, "unrecognized constraint subtype: %d",
(int) constr->contype);
@@ -2934,8 +2944,16 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
switch (constr->contype)
{
case CONSTR_CHECK:
+ if (constr->is_no_inherit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("check constraints for domains cannot be marked NO INHERIT")));
+ break;
case CONSTR_NOTNULL:
- /* processed below */
+ if (constr->is_no_inherit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("NOT NULL constraints for domains cannot be marked NO INHERIT")));
break;
case CONSTR_UNIQUE:
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index f65b66345a..f4131c2696 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -87,6 +87,90 @@ drop domain domainvarchar restrict;
drop domain domainnumeric restrict;
drop domain domainint4 restrict;
drop domain domaintext;
+--error case.
+create table t1(a int primary key);
+create domain d_fail as int4 constraint cc references t1;
+ERROR: foreign key constraints not possible for domains
+create domain d_fail as int4 not null no inherit;
+ERROR: not-null constraints for domains cannot be marked NO INHERIT
+create domain d_fail as int4 not null null;
+ERROR: conflicting NULL/NOT NULL constraints
+create domain d_fail as int4 not null default 3 default 3;
+ERROR: multiple default expressions
+create domain d_fail as int4 unique;
+ERROR: unique constraints not possible for domains
+create domain d_fail as int4 PRIMARY key;
+ERROR: primary key constraints not possible for domains
+create domain d_fail as int4 constraint cc generated by default as identity;
+ERROR: identity columns are not supported on domains
+create domain d_fail as int4 constraint cc GENERATED ALWAYS AS (2) STORED;
+ERROR: generated columns are not supported on domains
+create domain d_fail as int4 constraint cc check (value > 1) INITIALLY DEFERRED;
+ERROR: specifying constraint deferrability not supported for domains
+create domain d_fail as int4 constraint cc check(values > 1) no inherit;
+ERROR: check constraints for domains cannot be marked NO INHERIT
+create domain d_fail as int4 constraint cc check(values > 1) DEFERRABLE INITIALLY DEFERRED ;
+ERROR: specifying constraint deferrability not supported for domains
+create domain d_fail as int4 constraint cc check(values > 1) DEFERRABLE NOT DEFERRABLE ;
+ERROR: specifying constraint deferrability not supported for domains
+create domain d_fail as int4 constraint cc check(values > 1) INITIALLY IMMEDIATE;
+ERROR: specifying constraint deferrability not supported for domains
+create domain d_fail as int4 constraint cc exclude (values > 1) INITIALLY IMMEDIATE;
+ERROR: syntax error at or near "exclude"
+LINE 1: create domain d_fail as int4 constraint cc exclude (values >...
+ ^
+create domain d_int as int4;
+ALTER DOMAIN d_int ADD constraint ne not null no inherit;
+ERROR: NOT NULL constraints cannot be marked NO INHERIT
+LINE 1: ALTER DOMAIN d_int ADD constraint ne not null no inherit;
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY DEFERRED;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY ...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) no inherit;
+ERROR: check constraints for domains cannot be marked NO INHERIT
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE INITIALLY DEFERRED;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERRABLE;
+ERROR: conflicting constraint properties
+LINE 1: ...int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERR...
+ ^
+ALTER DOMAIN d_int ADD constraint cc1 check(value > 1) INITIALLY IMMEDIATE; --ok
+ALTER DOMAIN d_int ADD constraint cc2 check(value > 1) NOT DEFERRABLE; --ok
+--with not valid
+ALTER DOMAIN d_int ADD constraint ne not null no inherit not valid;
+ERROR: NOT NULL constraints cannot be marked NOT VALID
+LINE 1: ALTER DOMAIN d_int ADD constraint ne not null no inherit not...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE not valid;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY DEFERRED not valid;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY ...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) no inherit not valid;
+ERROR: check constraints for domains cannot be marked NO INHERIT
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE INITIALLY DEFERRED not valid;
+ERROR: CHECK constraints cannot be marked DEFERRABLE
+LINE 1: ...R DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE...
+ ^
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERRABLE not valid;
+ERROR: conflicting constraint properties
+LINE 1: ...int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERR...
+ ^
+ALTER DOMAIN d_int ADD constraint cc3 check(value > 1) NOT DEFERRABLE not valid; --ok
+ALTER DOMAIN d_int ADD constraint cc4 check(value > 1) INITIALLY IMMEDIATE not valid; --ok
+DROP DOMAIN d_int;
+DROP TABLE t1;
-- Test non-error-throwing input
create domain positiveint int4 check(value > 0);
create domain weirdfloat float8 check((1 / value) < 10);
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index b5a70ee8be..60ed54df19 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -68,6 +68,43 @@ drop domain domainnumeric restrict;
drop domain domainint4 restrict;
drop domain domaintext;
+--error case.
+create table t1(a int primary key);
+create domain d_fail as int4 constraint cc references t1;
+create domain d_fail as int4 not null no inherit;
+create domain d_fail as int4 not null null;
+create domain d_fail as int4 not null default 3 default 3;
+create domain d_fail as int4 unique;
+create domain d_fail as int4 PRIMARY key;
+create domain d_fail as int4 constraint cc generated by default as identity;
+create domain d_fail as int4 constraint cc GENERATED ALWAYS AS (2) STORED;
+create domain d_fail as int4 constraint cc check (value > 1) INITIALLY DEFERRED;
+create domain d_fail as int4 constraint cc check(values > 1) no inherit;
+create domain d_fail as int4 constraint cc check(values > 1) DEFERRABLE INITIALLY DEFERRED ;
+create domain d_fail as int4 constraint cc check(values > 1) DEFERRABLE NOT DEFERRABLE ;
+create domain d_fail as int4 constraint cc check(values > 1) INITIALLY IMMEDIATE;
+create domain d_fail as int4 constraint cc exclude (values > 1) INITIALLY IMMEDIATE;
+
+create domain d_int as int4;
+ALTER DOMAIN d_int ADD constraint ne not null no inherit;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY DEFERRED;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) no inherit;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE INITIALLY DEFERRED;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERRABLE;
+ALTER DOMAIN d_int ADD constraint cc1 check(value > 1) INITIALLY IMMEDIATE; --ok
+ALTER DOMAIN d_int ADD constraint cc2 check(value > 1) NOT DEFERRABLE; --ok
+--with not valid
+ALTER DOMAIN d_int ADD constraint ne not null no inherit not valid;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE not valid;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) INITIALLY DEFERRED not valid;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) no inherit not valid;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE INITIALLY DEFERRED not valid;
+ALTER DOMAIN d_int ADD constraint cc check(value > 1) DEFERRABLE NOT DEFERRABLE not valid;
+ALTER DOMAIN d_int ADD constraint cc3 check(value > 1) NOT DEFERRABLE not valid; --ok
+ALTER DOMAIN d_int ADD constraint cc4 check(value > 1) INITIALLY IMMEDIATE not valid; --ok
+DROP DOMAIN d_int;
+DROP TABLE t1;
-- Test non-error-throwing input
--
2.34.1
