diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/inherit.out | 50 | ||||
| -rw-r--r-- | src/test/regress/expected/sanity_check.out | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/inherit.sql | 39 |
3 files changed, 91 insertions, 0 deletions
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index d8b5b1d44eb..9d374fe6c4a 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1090,6 +1090,56 @@ Inherits: test_foreign_constraints DROP TABLE test_foreign_constraints_inh; DROP TABLE test_foreign_constraints; DROP TABLE test_primary_constraints; +-- Test that parent and child CHECK constraints can be created in either order +create table p1(f1 int); +create table p1_c1() inherits(p1); +alter table p1 add constraint inh_check_constraint1 check (f1 > 0); +alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); +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 +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) + +drop table p1 cascade; +NOTICE: drop cascades to table p1_c1 +-- Test that a valid child can have not-valid parent, but not vice versa +create table invalid_check_con(f1 int); +create table invalid_check_con_child() inherits(invalid_check_con); +alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; +alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail +ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child" +alter table invalid_check_con_child drop constraint inh_check_constraint; +insert into invalid_check_con values(0); +alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); +alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; +NOTICE: merging constraint "inh_check_constraint" with inherited definition +insert into invalid_check_con values(0); -- fail +ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint" +DETAIL: Failing row contains (0). +insert into invalid_check_con_child values(0); -- fail +ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint" +DETAIL: Failing row contains (0). +select conrelid::regclass::text as relname, conname, + convalidated, conislocal, coninhcount, connoinherit +from pg_constraint where conname like 'inh\_check\_constraint%' +order by 1, 2; + relname | conname | convalidated | conislocal | coninhcount | connoinherit +-------------------------+----------------------+--------------+------------+-------------+-------------- + invalid_check_con | inh_check_constraint | f | t | 0 | f + invalid_check_con_child | inh_check_constraint | t | t | 1 | f +(2 rows) + +-- We don't drop the invalid_check_con* tables, to test dump/reload with -- -- Test parameterized append plans for inheritance trees -- diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 1c087a39035..b1ebcf60d25 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -62,6 +62,8 @@ int2_tbl|f int4_tbl|f int8_tbl|f interval_tbl|f +invalid_check_con|f +invalid_check_con_child|f iportaltest|f kd_point_tbl|t line_tbl|f diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index b307a5049bf..6b1df754a62 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -334,6 +334,45 @@ DROP TABLE test_foreign_constraints_inh; DROP TABLE test_foreign_constraints; DROP TABLE test_primary_constraints; +-- Test that parent and child CHECK constraints can be created in either order +create table p1(f1 int); +create table p1_c1() inherits(p1); + +alter table p1 add constraint inh_check_constraint1 check (f1 > 0); +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 +from pg_constraint where conname like 'inh\_check\_constraint%' +order by 1, 2; + +drop table p1 cascade; + +-- Test that a valid child can have not-valid parent, but not vice versa +create table invalid_check_con(f1 int); +create table invalid_check_con_child() inherits(invalid_check_con); + +alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; +alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail +alter table invalid_check_con_child drop constraint inh_check_constraint; + +insert into invalid_check_con values(0); + +alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); +alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; + +insert into invalid_check_con values(0); -- fail +insert into invalid_check_con_child values(0); -- fail + +select conrelid::regclass::text as relname, conname, + convalidated, conislocal, coninhcount, connoinherit +from pg_constraint where conname like 'inh\_check\_constraint%' +order by 1, 2; + +-- We don't drop the invalid_check_con* tables, to test dump/reload with + -- -- Test parameterized append plans for inheritance trees -- |