scratch3.sql

application/sql

Download

--pk partitioned multi level, FK not partitioned
--test ALTER TABLE VALIDATE CONSTRAINT
begin;
drop table if exists fk;
drop table if exists pk;
create table pk(i int, b int, primary key(i ,b)) partition by range (i);
create table pk_1 partition of pk for values from (0) to (10) partition by list(b);
create table pk_1_p1 partition OF pk_1 for values in (0, 1, 2);
create table pk_2 partition of pk for values from (10) to (12);
insert into pk values (0, 1), (1,2);
create table fk(i int, b int);
insert into fk values (0, 1), (1,3);
alter table fk add constraint fk_i_fkey foreign key(i, b) references pk not valid;
commit;

alter table fk validate constraint fk_i_fkey; --error
delete from fk where i = 1 and b = 3;
alter table fk validate constraint fk_i_fkey; --ok

select oid, conname, conparentid, contype, conenforced, convalidated,
	   conrelid, conrelid::regclass,
	   confrelid::regclass, confrelid as confrelid
from pg_constraint
where conrelid::regclass::text in ('fk', 'fk_1', 'fk_2') order by oid;
------------------------------------------------------------------------------------
--pk partitioned, FK partitioned
--test ALTER TABLE VALIDATE CONSTRAINT
begin;
drop table if exists fk;
drop table if exists pk;
create table pk(i int, b int, primary key(i ,b)) partition by range (i);
create table pk_1 partition of pk for values from (0) to (10) partition by list(b);
create table pk_1_p1 partition OF pk_1 for values in (0, 1, 2);
create table pk_2 partition of pk for values from (10) to (12);
insert into pk values (0, 1), (1,2);

create table fk(i int, b int) partition by range (i);
create table fk_1 partition of fk for values from (0) to (10) partition by list(b);
create table fk_1_p1 partition OF fk_1 for values in (0, 1, 2,3 );
create table fk_2 partition of fk for values from (10) to (12);
insert into fk values (0, 1), (1,3);
alter table fk add constraint fk_i_fkey foreign key(i, b) references pk not valid;
commit;

alter table fk validate constraint fk_i_fkey; --error
delete from fk where i = 1 and b = 3;
alter table fk validate constraint fk_i_fkey; --ok

select oid, conname, conparentid, contype, conenforced, convalidated,
	   conrelid, conrelid::regclass,
	   confrelid::regclass, confrelid as confrelid
from pg_constraint
where conrelid::regclass::text in ('fk', 'fk_1', 'fk_1_p1', 'fk_2') order by oid;
------------------------------------------------------------------------------------
--pk partitioned, FK not partitioned
--test ALTER TABLE ALTER CONSTRAINT ENFORCED
begin;
drop table if exists fk cascade;
drop table if exists pk cascade;
create table pk(i int primary key) partition by range (i);
create table pk_1 partition of pk for values from (0) to (1);
create table pk_2 partition of pk for values from (1) to (2);
insert into pk values (0), (1);

create table fk(i int);
insert into fk values (1);
insert into fk values (2);
alter table fk add constraint fk_i_fkey foreign key(i) references pk not enforced;
commit;
alter table fk alter constraint fk_i_fkey enforced; --error
delete from fk where i = 2;
alter table fk alter constraint fk_i_fkey enforced; --ok

select oid, conname, conparentid, contype, conenforced, convalidated,
	   conrelid, conrelid::regclass,
	   confrelid::regclass, confrelid as confrelid
from pg_constraint
where conrelid::regclass::text in ('fk', 'fk_1', 'fk_2') order by oid;

------------------------------------------------------------------------------------
--pk partitioned, fk partitioned, both multi level
--test ALTER TABLE ALTER CONSTRAINT ENFORCED
begin;
drop table if exists fk;
drop table if exists pk;
create table pk(i int, b int, primary key(i ,b)) partition by range (i);
create table pk_1 partition of pk for values from (0) to (10) partition by list(b);
create table pk_1_p1 partition OF pk_1 for values in (0, 1, 2);
create table pk_2 partition of pk for values from (10) to (12);
insert into pk values (0, 1), (1,2);

create table fk(i int, b int) partition by range (i);
create table fk_1 partition of fk for values from (0) to (10) partition by list(b);
create table fk_1_p1 partition OF fk_1 for values in (0, 1, 2,3 );
create table fk_2 partition of fk for values from (10) to (12);
insert into fk values (0, 1), (1,3);
alter table fk add constraint fk_i_fkey foreign key(i, b) references pk not enforced;
commit;

alter table fk alter constraint fk_i_fkey enforced; --error
delete from fk where i = 1 and b = 3;
alter table fk alter constraint fk_i_fkey enforced; --ok

select oid, conname, conparentid, contype, conenforced, convalidated,
	   conrelid, conrelid::regclass,
	   confrelid::regclass, confrelid as confrelid
from pg_constraint
where conrelid::regclass::text in ('fk', 'fk_1', 'fk_1_p1', 'fk_2') order by oid;
------------------------------------------------------------------------------------