v3_13.9.log
text/x-log
Filename: v3_13.9.log
Type: text/x-log
Part: 1
select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.1 20221121 (Red Hat 12.2.1-4), 64-bit
(1 row)
drop function if exists show_constraints();
DROP FUNCTION
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;
$$;
CREATE FUNCTION
drop table if exists t1;
DROP TABLE
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
create table t1_a partition of t1 for values in (1);
CREATE TABLE
create table t1_def partition of t1 default;
CREATE TABLE
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
ALTER TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey1 | t1 | t1_a | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(5 rows)
insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1, 2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:34: ERROR: update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL: Key (c1, c2)=(1, 2) is still referenced from table "t1".
alter table t1 detach partition t1_a;
psql:ticket15010_v3.sql:36: ERROR: removing partition "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1"
DETAIL: Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey1 | t1 | t1_a | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(5 rows)
delete from t1;
DELETE 2
alter table t1 detach partition t1_a;
ALTER TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 |
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(4 rows)
alter table t1 attach partition t1_a for values in (1);
ALTER TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(4 rows)
insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1, 2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
DELETE 1
truncate t1;
TRUNCATE TABLE
drop table t1_a;
DROP TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(3 rows)
create table t1_a partition of t1 for values in (1);
CREATE TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(4 rows)
insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1, 2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
DELETE 1
truncate t1;
TRUNCATE TABLE
alter table t1 drop constraint t1_c1_old_c2_old_fkey;
ALTER TABLE
select * from show_constraints();
conname | t | tref | coparent
---------+---+------+----------
(0 rows)
drop table t1_a;
DROP TABLE
create table t1_a partition of t1 for values in (1);
CREATE TABLE
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
ALTER TABLE
select * from show_constraints();
conname | t | tref | coparent
------------------------+--------+--------+-----------------------
t1_c1_old_c2_old_fkey | t1 | t1 |
t1_c1_old_c2_old_fkey | t1_a | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey | t1_def | t1 | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey1 | t1 | t1_a | t1_c1_old_c2_old_fkey
t1_c1_old_c2_old_fkey2 | t1 | t1_def | t1_c1_old_c2_old_fkey
(5 rows)
insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1, 2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:87: ERROR: update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL: Key (c1, c2)=(1, 2) is still referenced from table "t1".