diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/constraints.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 194 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 81 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 119 | ||||
| -rw-r--r-- | src/test/regress/sql/inherit.sql | 7 |
5 files changed, 357 insertions, 52 deletions
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 4f39100fcdf..a719d2f74e9 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -745,13 +745,9 @@ ERROR: misplaced NOT ENFORCED clause LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); ^ ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; -ERROR: FOREIGN KEY constraints cannot be marked ENFORCED -LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; - ^ +ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl" ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; -ERROR: FOREIGN KEY constraints cannot be marked NOT ENFORCED -LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC... - ^ +ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl" DROP TABLE unique_tbl; -- -- EXCLUDE constraints diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 7f678349a8e..53810a0fde9 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1,21 +1,49 @@ -- -- FOREIGN KEY -- --- MATCH FULL +-- NOT ENFORCED -- -- First test, check and cascade -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); -CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); --- Insert test data into PKTABLE +CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED, + ftest2 int ); +-- Inserting into the foreign key table will not result in an error, even if +-- there is no matching key in the referenced table. +INSERT INTO FKTABLE VALUES (1, 2); +INSERT INTO FKTABLE VALUES (2, 3); +-- Check FKTABLE +SELECT * FROM FKTABLE; + ftest1 | ftest2 +--------+-------- + 1 | 2 + 2 | 3 +(2 rows) + +-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered, +-- as it was previously in a valid state. +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED; +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(1) is not present in table "pktable". +-- Insert referenced data that satisfies the constraint, then attempt to +-- change it. INSERT INTO PKTABLE VALUES (1, 'Test1'); INSERT INTO PKTABLE VALUES (2, 'Test2'); +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED; +-- Any further inserts will fail due to the enforcement. +INSERT INTO FKTABLE VALUES (3, 4); +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(3) is not present in table "pktable". +-- +-- MATCH FULL +-- +-- First test, check and cascade +-- +-- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (3, 'Test3'); INSERT INTO PKTABLE VALUES (4, 'Test4'); INSERT INTO PKTABLE VALUES (5, 'Test5'); -- Insert successful rows into FK TABLE -INSERT INTO FKTABLE VALUES (1, 2); -INSERT INTO FKTABLE VALUES (2, 3); INSERT INTO FKTABLE VALUES (3, 4); INSERT INTO FKTABLE VALUES (NULL, 1); -- Insert a failed row into FK TABLE @@ -351,6 +379,43 @@ INSERT INTO FKTABLE VALUES (1, NULL); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey" DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. +-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa +ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED; +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + condeferrable | condeferred | conenforced | convalidated +---------------+-------------+-------------+-------------- + t | t | f | f +(1 row) + +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + condeferrable | condeferred | conenforced | convalidated +---------------+-------------+-------------+-------------- + t | t | f | f +(1 row) + +-- Enforceability also changes the validate state, as data validation will be +-- performed during this transformation. +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + condeferrable | condeferred | conenforced | convalidated +---------------+-------------+-------------+-------------- + t | t | t | t +(1 row) + +-- Can change enforceability and deferrability together +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + condeferrable | condeferred | conenforced | convalidated +---------------+-------------+-------------+-------------- + f | f | f | f +(1 row) + DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- MATCH SIMPLE @@ -1276,6 +1341,13 @@ INSERT INTO fktable VALUES (0, 20); ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" DETAIL: Key (fk)=(20) is not present in table "pktable". COMMIT; +ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED; +BEGIN; +-- doesn't match FK, but no error. +UPDATE pktable SET id = 10 WHERE id = 5; +-- doesn't match PK, but no error. +INSERT INTO fktable VALUES (0, 20); +ROLLBACK; -- try additional syntax ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE; -- illegal options @@ -1289,6 +1361,14 @@ ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID; ERROR: FOREIGN KEY constraints cannot be marked NOT VALID LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID; ^ +ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED; +ERROR: conflicting constraint properties +LINE 1: ...fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORC... + ^ +CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED); +ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed +LINE 1: ...ABLE fktable2 (fk int references pktable ENFORCED NOT ENFORC... + ^ -- test order of firing of FK triggers when several RI-induced changes need to -- be made to the same row. This was broken by subtransaction-related -- changes in 8.0. @@ -1586,10 +1666,14 @@ ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1; CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int); ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3; ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000); -ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b) + REFERENCES fk_notpartitioned_pk NOT ENFORCED; CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int); ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2; +ALTER TABLE fk_partitioned_fk_2 ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b) + REFERENCES fk_notpartitioned_pk NOT ENFORCED; ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000); +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED; CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int) PARTITION BY HASH (a); ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2, @@ -1665,6 +1749,67 @@ Indexes: Referenced by: TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) +-- Check the exsting FK trigger +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + conname | tgrel | tgname | tgtype +----------------------------+-----------------------+--------------------------+-------- + fk_partitioned_fk_a_b_fkey | fk_notpartitioned_pk | RI_ConstraintTrigger_a_N | 9 + fk_partitioned_fk_a_b_fkey | fk_notpartitioned_pk | RI_ConstraintTrigger_a_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_2 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_2 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_0 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_0 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_1 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_1 | RI_ConstraintTrigger_c_N | 17 +(14 rows) + +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED; +-- No triggers +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + conname | tgrel | tgname | tgtype +---------+-------+--------+-------- +(0 rows) + +-- Changing it back to ENFORCED will recreate the necessary triggers. +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED; +-- Should be exactly the same number of triggers found as before +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + conname | tgrel | tgname | tgtype +----------------------------+-----------------------+--------------------------+-------- + fk_partitioned_fk_a_b_fkey | fk_notpartitioned_pk | RI_ConstraintTrigger_a_N | 9 + fk_partitioned_fk_a_b_fkey | fk_notpartitioned_pk | RI_ConstraintTrigger_a_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_1 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_2 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_2 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_0 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_0 | RI_ConstraintTrigger_c_N | 17 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_1 | RI_ConstraintTrigger_c_N | 5 + fk_partitioned_fk_a_b_fkey | fk_partitioned_fk_3_1 | RI_ConstraintTrigger_c_N | 17 +(14 rows) + ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; @@ -1963,6 +2108,43 @@ Foreign-key constraints: TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DROP TABLE fk_partitioned_fk_2; +CREATE TABLE fk_partitioned_fk_2 (b int, a int, + CONSTRAINT fk_part_con FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED); +-- fail -- cannot merge constraints with different enforceability. +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +ERROR: constraint "fk_partitioned_fk_a_b_fkey" enforceability conflicts with constraint "fk_part_con" on relation "fk_partitioned_fk_2" +-- If the constraint is modified to match the enforceability of the parent, it will work. +BEGIN; +-- change child constraint +ALTER TABLE fk_partitioned_fk_2 ALTER CONSTRAINT fk_part_con ENFORCED; +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +\d fk_partitioned_fk_2 + Table "public.fk_partitioned_fk_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + b | integer | | | + a | integer | | | +Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502) +Foreign-key constraints: + TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE + +ROLLBACK; +BEGIN; +-- or change parent constraint +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED; +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +\d fk_partitioned_fk_2 + Table "public.fk_partitioned_fk_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + b | integer | | | + a | integer | | | +Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502) +Foreign-key constraints: + TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED + +ROLLBACK; +DROP TABLE fk_partitioned_fk_2; CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a); CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100); CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL); diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 4d07d0bd79b..c01e9d5244f 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1332,6 +1332,13 @@ 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 +alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced; +alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced; +NOTICE: merging constraint "inh_check_constraint9" with inherited definition +-- the not-valid state of the child constraint will be ignored here. +alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced; +NOTICE: merging constraint "inh_check_constraint10" 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 @@ -1356,39 +1363,47 @@ ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constrain select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced, convalidated from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; - relname | conname | conislocal | coninhcount | conenforced | convalidated ----------+-----------------------+------------+-------------+-------------+-------------- - p1 | inh_check_constraint1 | t | 0 | t | t - p1 | inh_check_constraint2 | t | 0 | t | t - p1 | inh_check_constraint3 | t | 0 | f | f - p1 | inh_check_constraint4 | t | 0 | f | f - p1 | inh_check_constraint5 | t | 0 | f | f - p1 | inh_check_constraint6 | t | 0 | f | f - p1 | inh_check_constraint8 | t | 0 | t | t - p1_c1 | inh_check_constraint1 | t | 1 | t | t - p1_c1 | inh_check_constraint2 | t | 1 | t | t - p1_c1 | inh_check_constraint3 | t | 1 | f | f - p1_c1 | inh_check_constraint4 | t | 1 | f | f - p1_c1 | inh_check_constraint5 | t | 1 | t | t - p1_c1 | inh_check_constraint6 | t | 1 | t | t - p1_c1 | inh_check_constraint7 | t | 0 | f | f - p1_c1 | inh_check_constraint8 | f | 1 | t | t - p1_c2 | inh_check_constraint1 | f | 1 | t | t - p1_c2 | inh_check_constraint2 | f | 1 | t | t - p1_c2 | inh_check_constraint3 | f | 1 | f | f - p1_c2 | inh_check_constraint4 | t | 1 | t | t - p1_c2 | inh_check_constraint5 | f | 1 | f | f - p1_c2 | inh_check_constraint6 | f | 1 | f | f - p1_c2 | inh_check_constraint8 | f | 1 | t | t - p1_c3 | inh_check_constraint1 | f | 2 | t | t - p1_c3 | inh_check_constraint2 | f | 2 | t | t - p1_c3 | inh_check_constraint3 | f | 2 | f | f - p1_c3 | inh_check_constraint4 | f | 2 | f | f - p1_c3 | inh_check_constraint5 | f | 2 | t | t - p1_c3 | inh_check_constraint6 | f | 2 | t | t - p1_c3 | inh_check_constraint7 | f | 1 | f | f - p1_c3 | inh_check_constraint8 | f | 2 | t | t -(30 rows) + relname | conname | conislocal | coninhcount | conenforced | convalidated +---------+------------------------+------------+-------------+-------------+-------------- + p1 | inh_check_constraint1 | t | 0 | t | t + p1 | inh_check_constraint10 | t | 0 | f | f + p1 | inh_check_constraint2 | t | 0 | t | t + p1 | inh_check_constraint3 | t | 0 | f | f + p1 | inh_check_constraint4 | t | 0 | f | f + p1 | inh_check_constraint5 | t | 0 | f | f + p1 | inh_check_constraint6 | t | 0 | f | f + p1 | inh_check_constraint8 | t | 0 | t | t + p1 | inh_check_constraint9 | t | 0 | f | f + p1_c1 | inh_check_constraint1 | t | 1 | t | t + p1_c1 | inh_check_constraint10 | t | 1 | t | t + p1_c1 | inh_check_constraint2 | t | 1 | t | t + p1_c1 | inh_check_constraint3 | t | 1 | f | f + p1_c1 | inh_check_constraint4 | t | 1 | f | f + p1_c1 | inh_check_constraint5 | t | 1 | t | t + p1_c1 | inh_check_constraint6 | t | 1 | t | t + p1_c1 | inh_check_constraint7 | t | 0 | f | f + p1_c1 | inh_check_constraint8 | f | 1 | t | t + p1_c1 | inh_check_constraint9 | t | 1 | t | f + p1_c2 | inh_check_constraint1 | f | 1 | t | t + p1_c2 | inh_check_constraint10 | f | 1 | f | f + p1_c2 | inh_check_constraint2 | f | 1 | t | t + p1_c2 | inh_check_constraint3 | f | 1 | f | f + p1_c2 | inh_check_constraint4 | t | 1 | t | t + p1_c2 | inh_check_constraint5 | f | 1 | f | f + p1_c2 | inh_check_constraint6 | f | 1 | f | f + p1_c2 | inh_check_constraint8 | f | 1 | t | t + p1_c2 | inh_check_constraint9 | f | 1 | f | f + p1_c3 | inh_check_constraint1 | f | 2 | t | t + p1_c3 | inh_check_constraint10 | f | 2 | t | t + p1_c3 | inh_check_constraint2 | f | 2 | t | t + p1_c3 | inh_check_constraint3 | f | 2 | f | f + p1_c3 | inh_check_constraint4 | f | 2 | f | f + p1_c3 | inh_check_constraint5 | f | 2 | t | t + p1_c3 | inh_check_constraint6 | f | 2 | t | t + p1_c3 | inh_check_constraint7 | f | 1 | f | f + p1_c3 | inh_check_constraint8 | f | 2 | t | t + p1_c3 | inh_check_constraint9 | f | 2 | t | t +(38 rows) drop table p1 cascade; NOTICE: drop cascades to 3 other objects diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 44945b0453a..77c0c615630 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -2,23 +2,46 @@ -- FOREIGN KEY -- --- MATCH FULL +-- NOT ENFORCED -- -- First test, check and cascade -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); -CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); +CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED, + ftest2 int ); --- Insert test data into PKTABLE +-- Inserting into the foreign key table will not result in an error, even if +-- there is no matching key in the referenced table. +INSERT INTO FKTABLE VALUES (1, 2); +INSERT INTO FKTABLE VALUES (2, 3); + +-- Check FKTABLE +SELECT * FROM FKTABLE; + +-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered, +-- as it was previously in a valid state. +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED; + +-- Insert referenced data that satisfies the constraint, then attempt to +-- change it. INSERT INTO PKTABLE VALUES (1, 'Test1'); INSERT INTO PKTABLE VALUES (2, 'Test2'); +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED; + +-- Any further inserts will fail due to the enforcement. +INSERT INTO FKTABLE VALUES (3, 4); + +-- +-- MATCH FULL +-- +-- First test, check and cascade +-- +-- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (3, 'Test3'); INSERT INTO PKTABLE VALUES (4, 'Test4'); INSERT INTO PKTABLE VALUES (5, 'Test5'); -- Insert successful rows into FK TABLE -INSERT INTO FKTABLE VALUES (1, 2); -INSERT INTO FKTABLE VALUES (2, 3); INSERT INTO FKTABLE VALUES (3, 4); INSERT INTO FKTABLE VALUES (NULL, 1); @@ -230,6 +253,27 @@ INSERT INTO FKTABLE VALUES (1, NULL); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; +-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa +ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED; +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + +-- Enforceability also changes the validate state, as data validation will be +-- performed during this transformation. +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + +-- Can change enforceability and deferrability together +ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE; +SELECT condeferrable, condeferred, conenforced, convalidated +FROM pg_constraint WHERE conname = 'fk_con'; + DROP TABLE FKTABLE; DROP TABLE PKTABLE; @@ -968,12 +1012,25 @@ INSERT INTO fktable VALUES (0, 20); COMMIT; +ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED; + +BEGIN; + +-- doesn't match FK, but no error. +UPDATE pktable SET id = 10 WHERE id = 5; +-- doesn't match PK, but no error. +INSERT INTO fktable VALUES (0, 20); + +ROLLBACK; + -- try additional syntax ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE; -- illegal options ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED; ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT; ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID; +ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED; +CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED); -- test order of firing of FK triggers when several RI-induced changes need to -- be made to the same row. This was broken by subtransaction-related @@ -1184,11 +1241,14 @@ ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1; CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int); ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3; ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000); -ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk; +ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b) + REFERENCES fk_notpartitioned_pk NOT ENFORCED; CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int); ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2; +ALTER TABLE fk_partitioned_fk_2 ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b) + REFERENCES fk_notpartitioned_pk NOT ENFORCED; ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000); - +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED; CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int) PARTITION BY HASH (a); ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2, @@ -1234,6 +1294,32 @@ UPDATE fk_notpartitioned_pk SET b = 1502 WHERE a = 1500; UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500; -- check psql behavior \d fk_notpartitioned_pk + +-- Check the exsting FK trigger +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED; +-- No triggers +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + +-- Changing it back to ENFORCED will recreate the necessary triggers. +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED; + +-- Should be exactly the same number of triggers found as before +SELECT conname, tgrelid::regclass as tgrel, regexp_replace(tgname, '[0-9]+', 'N') as tgname, tgtype +FROM pg_trigger t JOIN pg_constraint c ON (t.tgconstraint = c.oid) +WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass) + UNION ALL SELECT 'fk_notpartitioned_pk'::regclass) +ORDER BY tgrelid, tgtype; + ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey; -- done. DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk; @@ -1441,6 +1527,25 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN \d fk_partitioned_fk_2 DROP TABLE fk_partitioned_fk_2; +CREATE TABLE fk_partitioned_fk_2 (b int, a int, + CONSTRAINT fk_part_con FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED); +-- fail -- cannot merge constraints with different enforceability. +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +-- If the constraint is modified to match the enforceability of the parent, it will work. +BEGIN; +-- change child constraint +ALTER TABLE fk_partitioned_fk_2 ALTER CONSTRAINT fk_part_con ENFORCED; +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +\d fk_partitioned_fk_2 +ROLLBACK; +BEGIN; +-- or change parent constraint +ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED; +ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502); +\d fk_partitioned_fk_2 +ROLLBACK; +DROP TABLE fk_partitioned_fk_2; + CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a); CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100); CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 941189761fd..9b4bd4606f9 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -481,6 +481,13 @@ 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; +alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced; +alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced; + +-- the not-valid state of the child constraint will be ignored here. +alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced; + create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); -- but reverse is not allowed |