diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/constraints.out | 40 | ||||
| -rw-r--r-- | src/test/regress/expected/create_table_like.out | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/domain.out | 22 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 94 | ||||
| -rw-r--r-- | src/test/regress/sql/alter_table.sql | 7 | ||||
| -rw-r--r-- | src/test/regress/sql/constraints.sql | 22 | ||||
| -rw-r--r-- | src/test/regress/sql/create_table_like.sql | 3 | ||||
| -rw-r--r-- | src/test/regress/sql/domain.sql | 10 | ||||
| -rw-r--r-- | src/test/regress/sql/inherit.sql | 47 |
10 files changed, 252 insertions, 13 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 12852aa612a..dd8cdec2905 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -507,11 +507,14 @@ ALTER TABLE attmp3 validate constraint attmpconstr; ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds +ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten_not_enforced CHECK (b > 10) NOT ENFORCED; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row DELETE FROM attmp3 WHERE NOT b > 10; ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds +ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten_not_enforced; -- fail +ERROR: cannot validate NOT ENFORCED constraint -- Test inherited NOT VALID CHECK constraints select * from attmp3; a | b @@ -1689,6 +1692,13 @@ alter table renameColumn add column w int; -- this should fail alter table only renameColumn add column x int; ERROR: column must be added to child tables too +-- this should work +alter table renameColumn add column x int check (x > 0) not enforced; +-- this should fail +alter table renameColumn add column y int check (x > 0) not enforced enforced; +ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed +LINE 1: ...Column add column y int check (x > 0) not enforced enforced; + ^ -- Test corner cases in dropping of inherited columns create table p1 (f1 int, f2 int); create table c1 (f1 int not null) inherits(p1); diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 71200c90ed3..692a69fe457 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -87,6 +87,25 @@ SELECT * FROM CHECK_TBL; 6 (3 rows) +CREATE TABLE NE_CHECK_TBL (x int, + CONSTRAINT CHECK_CON CHECK (x > 3) NOT ENFORCED); +INSERT INTO NE_CHECK_TBL VALUES (5); +INSERT INTO NE_CHECK_TBL VALUES (4); +INSERT INTO NE_CHECK_TBL VALUES (3); +INSERT INTO NE_CHECK_TBL VALUES (2); +INSERT INTO NE_CHECK_TBL VALUES (6); +INSERT INTO NE_CHECK_TBL VALUES (1); +SELECT * FROM NE_CHECK_TBL; + x +--- + 5 + 4 + 3 + 2 + 6 + 1 +(6 rows) + CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, CONSTRAINT SEQUENCE_CON @@ -120,7 +139,8 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq'), CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), - CHECK (x + z = 0)); + CHECK (x + z = 0) ENFORCED, /* no change it is a default */ + CONSTRAINT NE_INSERT_TBL_CON CHECK (x + z = 1) NOT ENFORCED); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con" DETAIL: Failing row contains (2, -NULL-, -2). @@ -715,6 +735,24 @@ SELECT * FROM unique_tbl; 3 | threex (5 rows) +-- enforcibility cannot be specified or set for unique constrain +CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); +ERROR: misplaced ENFORCED clause +LINE 1: CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); + ^ +CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); +ERROR: misplaced NOT ENFORCED clause +LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); + ^ +-- XXX: error message is misleading here +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; +ERROR: ALTER CONSTRAINT statement constraints cannot be marked ENFORCED +LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; + ^ +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; +ERROR: ALTER CONSTRAINT statement constraints cannot be marked NOT ENFORCED +LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC... + ^ DROP TABLE unique_tbl; -- -- EXCLUDE constraints diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index d091da5a1ef..e0613891351 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -315,7 +315,8 @@ Referenced by: DROP TABLE inhz; -- including storage and comments -CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); +CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, + b text CHECK (length(b) > 100) NOT ENFORCED); CREATE INDEX ctlt1_b_key ON ctlt1 (b); CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1; @@ -366,6 +367,7 @@ CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INH NOTICE: merging column "a" with inherited definition NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition +NOTICE: merging constraint "ctlt1_b_check" with inherited definition \d+ ctlt1_inh Table "public.ctlt1_inh" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description @@ -374,6 +376,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Not-null constraints: "ctlt1_a_not_null" NOT NULL "a" (local, inherited) Inherits: ctlt1 @@ -395,6 +398,7 @@ NOTICE: merging multiple inherited definitions of column "a" c | text | | | | external | | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: @@ -415,6 +419,7 @@ Indexes: "ctlt13_like_expr_idx" btree ((a || c)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: @@ -440,6 +445,7 @@ Indexes: "ctlt_all_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "public.ctlt_all_a_b_stat" ON a, b FROM ctlt_all "public.ctlt_all_expr_stat" ON (a || b) FROM ctlt_all @@ -482,6 +488,7 @@ Indexes: "pg_attrdef_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "public.pg_attrdef_a_b_stat" ON a, b FROM public.pg_attrdef "public.pg_attrdef_expr_stat" ON (a || b) FROM public.pg_attrdef @@ -506,6 +513,7 @@ Indexes: "ctlt1_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "ctl_schema.ctlt1_a_b_stat" ON a, b FROM ctlt1 "ctl_schema.ctlt1_expr_stat" ON (a || b) FROM ctlt1 diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 7a2a717aeae..ba6f05eeb7d 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -1350,6 +1350,28 @@ select pg_basetype(1); -- expect NULL not error drop domain mytext cascade; NOTICE: drop cascades to type mytext_child_1 -- +-- Explicit enforceability specification not allowed +--- +CREATE DOMAIN constraint_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ERROR: specifying constraint enforceability not supported for domains +LINE 1: ...AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; + ^ +CREATE DOMAIN constraint_not_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +ERROR: specifying constraint enforceability not supported for domains +LINE 1: ...S int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORC... + ^ +CREATE DOMAIN constraint_enforced_dom AS int; +-- XXX misleading error messages +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ERROR: CHECK constraints cannot be marked ENFORCED +LINE 1: ...om ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; + ^ +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +ERROR: CHECK constraints cannot be marked NOT ENFORCED +LINE 1: ...m ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORC... + ^ +DROP DOMAIN constraint_enforced_dom; +-- -- Information schema -- SELECT * FROM information_schema.column_domain_usage diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index bb81f6d2b4d..dbf3835cb14 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1319,19 +1319,97 @@ NOTICE: merging constraint "inh_check_constraint1" with inherited definition alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); alter table p1 add constraint inh_check_constraint2 check (f1 < 10); NOTICE: merging constraint "inh_check_constraint2" with inherited definition -select conrelid::regclass::text as relname, conname, conislocal, coninhcount +alter table p1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +alter table p1_c1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +NOTICE: merging constraint "inh_check_constraint3" with inherited definition +alter table p1_c1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +NOTICE: merging constraint "inh_check_constraint4" with inherited definition +-- allowed to merge enforced constraint with parent's not enforced constraint +alter table p1_c1 add constraint inh_check_constraint5 check (f1 < 10) enforced; +alter table p1 add constraint inh_check_constraint5 check (f1 < 10) not enforced; +NOTICE: merging constraint "inh_check_constraint5" with inherited definition +alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced; +NOTICE: merging constraint "inh_check_constraint6" with inherited definition +create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); +NOTICE: merging column "f1" with inherited definition +NOTICE: merging constraint "inh_check_constraint4" with inherited definition +-- but reverse is not allowed +alter table p1_c1 add constraint inh_check_constraint7 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint7 check (f1 < 10) enforced; +ERROR: constraint "inh_check_constraint7" conflicts with NOT ENFORCED constraint on relation "p1_c1" +alter table p1 add constraint inh_check_constraint8 check (f1 < 10) enforced; +alter table p1_c1 add constraint inh_check_constraint8 check (f1 < 10) not enforced; +ERROR: constraint "inh_check_constraint8" conflicts with NOT ENFORCED constraint on relation "p1_c1" +create table p1_fail(f1 int constraint inh_check_constraint2 check (f1 < 10) not enforced) inherits(p1); +NOTICE: merging column "f1" with inherited definition +ERROR: constraint "inh_check_constraint2" conflicts with NOT ENFORCED constraint on relation "p1_fail" +-- constraints with different enforceability can be merged by marking them as ENFORCED +create table p1_c3() inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +-- but not allowed if the child constraint is explicitly asked to be NOT ENFORCED +create table p1_fail(f1 int constraint inh_check_constraint6 check (f1 < 10) not enforced) inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constraint on relation "p1_fail" +select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; - relname | conname | conislocal | coninhcount ----------+-----------------------+------------+------------- - p1 | inh_check_constraint1 | t | 0 - p1 | inh_check_constraint2 | t | 0 - p1_c1 | inh_check_constraint1 | t | 1 - p1_c1 | inh_check_constraint2 | t | 1 -(4 rows) + relname | conname | conislocal | coninhcount | conenforced +---------+-----------------------+------------+-------------+------------- + p1 | inh_check_constraint1 | t | 0 | t + p1 | inh_check_constraint2 | t | 0 | t + p1 | inh_check_constraint3 | t | 0 | f + p1 | inh_check_constraint4 | t | 0 | f + p1 | inh_check_constraint5 | t | 0 | f + p1 | inh_check_constraint6 | t | 0 | f + p1 | inh_check_constraint8 | t | 0 | t + p1_c1 | inh_check_constraint1 | t | 1 | t + p1_c1 | inh_check_constraint2 | t | 1 | t + p1_c1 | inh_check_constraint3 | t | 1 | f + p1_c1 | inh_check_constraint4 | t | 1 | f + p1_c1 | inh_check_constraint5 | t | 1 | t + p1_c1 | inh_check_constraint6 | t | 1 | t + p1_c1 | inh_check_constraint7 | t | 0 | f + p1_c1 | inh_check_constraint8 | f | 1 | t + p1_c2 | inh_check_constraint1 | f | 1 | t + p1_c2 | inh_check_constraint2 | f | 1 | t + p1_c2 | inh_check_constraint3 | f | 1 | f + p1_c2 | inh_check_constraint4 | t | 1 | t + p1_c2 | inh_check_constraint5 | f | 1 | f + p1_c2 | inh_check_constraint6 | f | 1 | f + p1_c2 | inh_check_constraint8 | f | 1 | t + p1_c3 | inh_check_constraint1 | f | 2 | t + p1_c3 | inh_check_constraint2 | f | 2 | t + p1_c3 | inh_check_constraint3 | f | 2 | f + p1_c3 | inh_check_constraint4 | f | 2 | f + p1_c3 | inh_check_constraint5 | f | 2 | t + p1_c3 | inh_check_constraint6 | f | 2 | t + p1_c3 | inh_check_constraint7 | f | 1 | f + p1_c3 | inh_check_constraint8 | f | 2 | t +(30 rows) drop table p1 cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table p1_c1 +drop cascades to table p1_c2 +drop cascades to table p1_c3 +-- +-- Similarly, check the merging of existing constraints; a parent constraint +-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the +-- reverse is not allowed. +-- +create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); +alter table p1_c1 inherit p1; +drop table p1 cascade; NOTICE: drop cascades to table p1_c1 +create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); +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; -- -- Test DROP behavior of multiply-defined CHECK constraints -- diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index c88f9eaab04..84e93ef575e 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -387,10 +387,12 @@ ALTER TABLE attmp3 validate constraint attmpconstr; -- Try a non-verified CHECK constraint ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds +ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten_not_enforced CHECK (b > 10) NOT ENFORCED; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails DELETE FROM attmp3 WHERE NOT b > 10; ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds +ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten_not_enforced; -- fail -- Test inherited NOT VALID CHECK constraints select * from attmp3; @@ -1188,6 +1190,11 @@ alter table renameColumn add column w int; -- this should fail alter table only renameColumn add column x int; +-- this should work +alter table renameColumn add column x int check (x > 0) not enforced; + +-- this should fail +alter table renameColumn add column y int check (x > 0) not enforced enforced; -- Test corner cases in dropping of inherited columns diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index e607eb1fddb..d6742f83fb9 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -67,6 +67,18 @@ INSERT INTO CHECK_TBL VALUES (1); SELECT * FROM CHECK_TBL; +CREATE TABLE NE_CHECK_TBL (x int, + CONSTRAINT CHECK_CON CHECK (x > 3) NOT ENFORCED); + +INSERT INTO NE_CHECK_TBL VALUES (5); +INSERT INTO NE_CHECK_TBL VALUES (4); +INSERT INTO NE_CHECK_TBL VALUES (3); +INSERT INTO NE_CHECK_TBL VALUES (2); +INSERT INTO NE_CHECK_TBL VALUES (6); +INSERT INTO NE_CHECK_TBL VALUES (1); + +SELECT * FROM NE_CHECK_TBL; + CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, @@ -92,7 +104,8 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq'), CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), - CHECK (x + z = 0)); + CHECK (x + z = 0) ENFORCED, /* no change it is a default */ + CONSTRAINT NE_INSERT_TBL_CON CHECK (x + z = 1) NOT ENFORCED); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); @@ -518,6 +531,13 @@ COMMIT; SELECT * FROM unique_tbl; +-- enforcibility cannot be specified or set for unique constrain +CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); +CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); +-- XXX: error message is misleading here +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; + DROP TABLE unique_tbl; -- diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index dea8942c71f..a41f8b83d77 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -128,7 +128,8 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES); DROP TABLE inhz; -- including storage and comments -CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); +CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, + b text CHECK (length(b) > 100) NOT ENFORCED); CREATE INDEX ctlt1_b_key ON ctlt1 (b); CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index ad14de355ac..b752a63ab5f 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -880,6 +880,16 @@ select pg_basetype(1); -- expect NULL not error drop domain mytext cascade; +-- +-- Explicit enforceability specification not allowed +--- +CREATE DOMAIN constraint_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +CREATE DOMAIN constraint_not_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +CREATE DOMAIN constraint_enforced_dom AS int; +-- XXX misleading error messages +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +DROP DOMAIN constraint_enforced_dom; -- -- Information schema diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index f51c70d6b03..49aae426f3c 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -468,13 +468,58 @@ alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); alter table p1 add constraint inh_check_constraint2 check (f1 < 10); -select conrelid::regclass::text as relname, conname, conislocal, coninhcount +alter table p1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +alter table p1_c1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; + +alter table p1_c1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; + +-- allowed to merge enforced constraint with parent's not enforced constraint +alter table p1_c1 add constraint inh_check_constraint5 check (f1 < 10) enforced; +alter table p1 add constraint inh_check_constraint5 check (f1 < 10) not enforced; + +alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced; + +create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); + +-- but reverse is not allowed +alter table p1_c1 add constraint inh_check_constraint7 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint7 check (f1 < 10) enforced; + +alter table p1 add constraint inh_check_constraint8 check (f1 < 10) enforced; +alter table p1_c1 add constraint inh_check_constraint8 check (f1 < 10) not enforced; + +create table p1_fail(f1 int constraint inh_check_constraint2 check (f1 < 10) not enforced) inherits(p1); + +-- constraints with different enforceability can be merged by marking them as ENFORCED +create table p1_c3() inherits(p1, p1_c1); + +-- but not allowed if the child constraint is explicitly asked to be NOT ENFORCED +create table p1_fail(f1 int constraint inh_check_constraint6 check (f1 < 10) not enforced) inherits(p1, p1_c1); + +select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; drop table p1 cascade; -- +-- Similarly, check the merging of existing constraints; a parent constraint +-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the +-- reverse is not allowed. +-- +create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); +alter table p1_c1 inherit p1; +drop table p1 cascade; + +create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); +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; + +-- -- Test DROP behavior of multiply-defined CHECK constraints -- create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); |