v3_13.7.log

text/x-log

Download

select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 13.7 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();
psql:ticket15010_v3.sql:3: NOTICE:  function show_constraints() does not exist, skipping
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;
psql:ticket15010_v3.sql:15: NOTICE:  table "t1" does not exist, skipping
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_a_c1_old_c2_old_fkey | t1_a   | t1_a   | t1_c1_old_c2_old_fkey
 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
(6 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:55: 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".
truncate t1;
TRUNCATE TABLE
drop table t1_a;
psql:ticket15010_v3.sql:58: ERROR:  cannot drop table t1_a because other objects depend on it
DETAIL:  constraint t1_c1_old_c2_old_fkey on table t1 depends on table t1_a
HINT:  Use DROP ... CASCADE to drop the dependent objects too.