ticket15010_v3.sql

select version();

drop function if exists show_constraints();
create function show_constraints() returns table(conname text, t text, tref text, coparent text) language sql as
$$
select co.conname, cl.relname as t, clref.relname as tref, coparent.conname as coparent
from pg_constraint co
join pg_class cl on cl.oid=co.conrelid
join pg_class clref on clref.oid=co.confrelid
left join pg_constraint coparent on coparent.oid=co.conparentid
where co.contype = 'f'
order by 1,2,3,4;
$$;

drop table if exists t1;
create table t1 (
  c1 bigint not null,
  c1_old bigint null,
  c2 bigint not null,
  c2_old bigint null,
  primary key (c1, c2)
  )
  partition by list (c1);
create table t1_a   partition of t1 for values in (1);
create table t1_def partition of t1 default;

alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

alter table t1 detach partition t1_a;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

delete from t1;

alter table t1 detach partition t1_a;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

alter table t1 attach partition t1_a for values in (1);
--\d+ t1
--\d+ t1_a
select * from show_constraints();

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

truncate t1;
drop table t1_a;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

create table t1_a   partition of t1 for values in (1);
--\d+ t1
--\d+ t1_a
select * from show_constraints();

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

truncate t1;
alter table t1 drop constraint t1_c1_old_c2_old_fkey;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

drop table t1_a;
create table t1_a   partition of t1 for values in (1);
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
--\d+ t1
--\d+ t1_a
select * from show_constraints();

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;