diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 28 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 19 |
2 files changed, 44 insertions, 3 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index f85c2ef78a8..76040a76011 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1921,7 +1921,31 @@ alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56" alter table fkpart0.fk_part_56_5 drop constraint fk_part_a_fkey; ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56_5" +-- verify that attaching and detaching partitions maintains the right set of +-- triggers +create schema fkpart1 + create table pkey (a int primary key) + create table fk_part (a int) partition by list (a) + create table fk_part_1 partition of fk_part for values in (1) partition by list (a) + create table fk_part_1_1 partition of fk_part_1 for values in (1); +alter table fkpart1.fk_part add foreign key (a) references fkpart1.pkey; +insert into fkpart1.fk_part values (1); -- should fail +ERROR: insert or update on table "fk_part_1_1" violates foreign key constraint "fk_part_a_fkey" +DETAIL: Key (a)=(1) is not present in table "pkey". +insert into fkpart1.pkey values (1); +insert into fkpart1.fk_part values (1); +delete from fkpart1.pkey where a = 1; -- should fail +ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part" +DETAIL: Key (a)=(1) is still referenced from table "fk_part". +alter table fkpart1.fk_part detach partition fkpart1.fk_part_1; +create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2); +insert into fkpart1.fk_part_1 values (2); -- should fail +ERROR: insert or update on table "fk_part_1_2" violates foreign key constraint "fk_part_a_fkey" +DETAIL: Key (a)=(2) is not present in table "pkey". +delete from fkpart1.pkey where a = 1; +ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1" +DETAIL: Key (a)=(1) is still referenced from table "fk_part_1". \set VERBOSITY terse \\ -- suppress cascade details -drop schema fkpart0 cascade; -NOTICE: drop cascades to 2 other objects +drop schema fkpart0, fkpart1 cascade; +NOTICE: drop cascades to 5 other objects \set VERBOSITY default diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index c3a7cfcc019..9ed1166c666 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1375,6 +1375,23 @@ create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56 alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey; alter table fkpart0.fk_part_56_5 drop constraint fk_part_a_fkey; +-- verify that attaching and detaching partitions maintains the right set of +-- triggers +create schema fkpart1 + create table pkey (a int primary key) + create table fk_part (a int) partition by list (a) + create table fk_part_1 partition of fk_part for values in (1) partition by list (a) + create table fk_part_1_1 partition of fk_part_1 for values in (1); +alter table fkpart1.fk_part add foreign key (a) references fkpart1.pkey; +insert into fkpart1.fk_part values (1); -- should fail +insert into fkpart1.pkey values (1); +insert into fkpart1.fk_part values (1); +delete from fkpart1.pkey where a = 1; -- should fail +alter table fkpart1.fk_part detach partition fkpart1.fk_part_1; +create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2); +insert into fkpart1.fk_part_1 values (2); -- should fail +delete from fkpart1.pkey where a = 1; + \set VERBOSITY terse \\ -- suppress cascade details -drop schema fkpart0 cascade; +drop schema fkpart0, fkpart1 cascade; \set VERBOSITY default |