Support foreign keys that reference partitioned tables

Previously, while primary keys could be made on partitioned tables, it
was not possible to define foreign keys that reference those primary
keys.  Now it is possible to do that.

Author: Álvaro Herrera
Reviewed-by: Amit Langote, Jesper Pedersen
Discussion: https://postgr.es/m/20181102234158.735b3fevta63msbj@alvherre.pgsql

5 files changed, 279 insertions, 0 deletions

diff --git a/src/test/isolation/expected/fk-partitioned-1.out b/src/test/isolation/expected/fk-partitioned-1.out
new file mode 100644
index 00000000000..aea2b6d56b4
--- /dev/null
+++ b/src/test/isolation/expected/fk-partitioned-1.out
@@ -0,0 +1,133 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1b s1d s1c s2b s2a s2c
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s1c: commit;
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s1d s2b s1c s2a s2c
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s2b: begin;
+step s1c: commit;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s1d s2b s2a s1c s2c
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1); <waiting ...>
+step s1c: commit;
+step s2a: <... completed>
+error in steps s1c s2a: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s2b s1d s1c s2a s2c
+step s1b: begin;
+step s2b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s1c: commit;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s2b s1d s2a s1c s2c
+step s1b: begin;
+step s2b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s2a: alter table pfk attach partition pfk1 for values in (1); <waiting ...>
+step s1c: commit;
+step s2a: <... completed>
+error in steps s1c s2a: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s2b s2a s1d s2c s1c
+step s1b: begin;
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s1d: delete from ppk1 where a = 1; <waiting ...>
+step s2c: commit;
+step s1d: <... completed>
+error in steps s2c s1d: ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s1b s2b s2a s2c s1d s1c
+step s1b: begin;
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s2c: commit;
+step s1d: delete from ppk1 where a = 1;
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s2b s1b s1d s1c s2a s2c
+step s2b: begin;
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s1c: commit;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s2b s1b s1d s2a s1c s2c
+step s2b: begin;
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+step s2a: alter table pfk attach partition pfk1 for values in (1); <waiting ...>
+step s1c: commit;
+step s2a: <... completed>
+error in steps s1c s2a: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s2b s1b s2a s1d s2c s1c
+step s2b: begin;
+step s1b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s1d: delete from ppk1 where a = 1; <waiting ...>
+step s2c: commit;
+step s1d: <... completed>
+error in steps s2c s1d: ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s2b s1b s2a s2c s1d s1c
+step s2b: begin;
+step s1b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s2c: commit;
+step s1d: delete from ppk1 where a = 1;
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s2b s2a s1b s1d s2c s1c
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1; <waiting ...>
+step s2c: commit;
+step s1d: <... completed>
+error in steps s2c s1d: ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s2b s2a s1b s2c s1d s1c
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s1b: begin;
+step s2c: commit;
+step s1d: delete from ppk1 where a = 1;
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s2b s2a s2c s1b s1d s1c
+step s2b: begin;
+step s2a: alter table pfk attach partition pfk1 for values in (1);
+step s2c: commit;
+step s1b: begin;
+step s1d: delete from ppk1 where a = 1;
+ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
diff --git a/src/test/isolation/expected/fk-partitioned-2.out b/src/test/isolation/expected/fk-partitioned-2.out
new file mode 100644
index 00000000000..722b615c6ea
--- /dev/null
+++ b/src/test/isolation/expected/fk-partitioned-2.out
@@ -0,0 +1,70 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1b s1d s2b s2i s1c s2c
+step s1b: begin;
+step s1d: delete from ppk where a = 1;
+step s2b: begin;
+step s2i: insert into pfk values (1); <waiting ...>
+step s1c: commit;
+step s2i: <... completed>
+error in steps s1c s2i: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s1d s2bs s2i s1c s2c
+step s1b: begin;
+step s1d: delete from ppk where a = 1;
+step s2bs: begin isolation level serializable; select 1;
+?column?       
+
+1              
+step s2i: insert into pfk values (1); <waiting ...>
+step s1c: commit;
+step s2i: <... completed>
+error in steps s1c s2i: ERROR:  could not serialize access due to concurrent update
+step s2c: commit;
+
+starting permutation: s1b s2b s1d s2i s1c s2c
+step s1b: begin;
+step s2b: begin;
+step s1d: delete from ppk where a = 1;
+step s2i: insert into pfk values (1); <waiting ...>
+step s1c: commit;
+step s2i: <... completed>
+error in steps s1c s2i: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+step s2c: commit;
+
+starting permutation: s1b s2bs s1d s2i s1c s2c
+step s1b: begin;
+step s2bs: begin isolation level serializable; select 1;
+?column?       
+
+1              
+step s1d: delete from ppk where a = 1;
+step s2i: insert into pfk values (1); <waiting ...>
+step s1c: commit;
+step s2i: <... completed>
+error in steps s1c s2i: ERROR:  could not serialize access due to concurrent update
+step s2c: commit;
+
+starting permutation: s1b s2b s2i s1d s2c s1c
+step s1b: begin;
+step s2b: begin;
+step s2i: insert into pfk values (1);
+step s1d: delete from ppk where a = 1; <waiting ...>
+step s2c: commit;
+step s1d: <... completed>
+error in steps s2c s1d: ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
+
+starting permutation: s1b s2bs s2i s1d s2c s1c
+step s1b: begin;
+step s2bs: begin isolation level serializable; select 1;
+?column?       
+
+1              
+step s2i: insert into pfk values (1);
+step s1d: delete from ppk where a = 1; <waiting ...>
+step s2c: commit;
+step s1d: <... completed>
+error in steps s2c s1d: ERROR:  update or delete on table "ppk1" violates foreign key constraint "pfk_a_fkey1" on table "pfk"
+step s1c: commit;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index f1ae50e5ba8..11cd24fc981 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -24,6 +24,8 @@ test: deadlock-soft-2
 test: fk-contention
 test: fk-deadlock
 test: fk-deadlock2
+test: fk-partitioned-1
+test: fk-partitioned-2
 test: eval-plan-qual
 test: lock-update-delete
 test: lock-update-traversal
diff --git a/src/test/isolation/specs/fk-partitioned-1.spec b/src/test/isolation/specs/fk-partitioned-1.spec
new file mode 100644
index 00000000000..4c760e89b34
--- /dev/null
+++ b/src/test/isolation/specs/fk-partitioned-1.spec
@@ -0,0 +1,45 @@
+# Verify that cloning a foreign key constraint to a partition ensures
+# that referenced values exist, even if they're being concurrently
+# deleted.
+setup {
+drop table if exists ppk, pfk, pfk1;
+  create table ppk (a int primary key) partition by list (a);
+  create table ppk1 partition of ppk for values in (1);
+  insert into ppk values (1);
+  create table pfk (a int references ppk) partition by list (a);
+  create table pfk1 (a int not null);
+  insert into pfk1 values (1);
+}
+
+session "s1"
+step "s1b"	{	begin; }
+step "s1d"	{	delete from ppk1 where a = 1; }
+step "s1c"	{	commit; }
+
+session "s2"
+step "s2b"	{	begin; }
+step "s2a"	{	alter table pfk attach partition pfk1 for values in (1); }
+step "s2c"	{	commit; }
+
+teardown	{	drop table ppk, pfk, pfk1; }
+
+permutation "s1b" "s1d" "s1c" "s2b" "s2a" "s2c"
+permutation "s1b" "s1d" "s2b" "s1c" "s2a" "s2c"
+permutation "s1b" "s1d" "s2b" "s2a" "s1c" "s2c"
+#permutation "s1b" "s1d" "s2b" "s2a" "s2c" "s1c"
+permutation "s1b" "s2b" "s1d" "s1c" "s2a" "s2c"
+permutation "s1b" "s2b" "s1d" "s2a" "s1c" "s2c"
+#permutation "s1b" "s2b" "s1d" "s2a" "s2c" "s1c"
+#permutation "s1b" "s2b" "s2a" "s1d" "s1c" "s2c"
+permutation "s1b" "s2b" "s2a" "s1d" "s2c" "s1c"
+permutation "s1b" "s2b" "s2a" "s2c" "s1d" "s1c"
+permutation "s2b" "s1b" "s1d" "s1c" "s2a" "s2c"
+permutation "s2b" "s1b" "s1d" "s2a" "s1c" "s2c"
+#permutation "s2b" "s1b" "s1d" "s2a" "s2c" "s1c"
+#permutation "s2b" "s1b" "s2a" "s1d" "s1c" "s2c"
+permutation "s2b" "s1b" "s2a" "s1d" "s2c" "s1c"
+permutation "s2b" "s1b" "s2a" "s2c" "s1d" "s1c"
+#permutation "s2b" "s2a" "s1b" "s1d" "s1c" "s2c"
+permutation "s2b" "s2a" "s1b" "s1d" "s2c" "s1c"
+permutation "s2b" "s2a" "s1b" "s2c" "s1d" "s1c"
+permutation "s2b" "s2a" "s2c" "s1b" "s1d" "s1c"
diff --git a/src/test/isolation/specs/fk-partitioned-2.spec b/src/test/isolation/specs/fk-partitioned-2.spec
new file mode 100644
index 00000000000..f1a76e801cf
--- /dev/null
+++ b/src/test/isolation/specs/fk-partitioned-2.spec
@@ -0,0 +1,29 @@
+# Make sure that FKs referencing partitioned tables actually work.
+setup {
+  drop table if exists ppk, pfk, pfk1;
+  create table ppk (a int primary key) partition by list (a);
+  create table ppk1 partition of ppk for values in (1);
+  insert into ppk values (1);
+  create table pfk (a int references ppk) partition by list (a);
+  create table pfk1 partition of pfk for values in (1);
+}
+
+session "s1"
+step "s1b"	{	begin; }
+step "s1d"	{	delete from ppk where a = 1; }
+step "s1c"	{	commit; }
+
+session "s2"
+step "s2b"	{	begin; }
+step "s2bs"	{	begin isolation level serializable; select 1; }
+step "s2i"	{	insert into pfk values (1); }
+step "s2c"	{	commit; }
+
+teardown	{	drop table ppk, pfk, pfk1; }
+
+permutation "s1b" "s1d"  "s2b"  "s2i" "s1c" "s2c"
+permutation "s1b" "s1d"  "s2bs" "s2i" "s1c" "s2c"
+permutation "s1b" "s2b"  "s1d"  "s2i" "s1c" "s2c"
+permutation "s1b" "s2bs" "s1d"  "s2i" "s1c" "s2c"
+permutation "s1b" "s2b"  "s2i"  "s1d" "s2c" "s1c"
+permutation "s1b" "s2bs" "s2i"  "s1d" "s2c" "s1c"