Thread: BUG #15792: Dependency remains on foreign key constraint when child detached from declarative partition.

The following bug has been logged on the website:

Bug reference:      15792
Logged by:          Stefan van der Berg
Email address:      svb007@gmail.com
PostgreSQL version: 11.2
Operating system:   CentOS Linux release 7.6.1810 (Core)
Description:

This can be recreated using the following code :
drop table if exists public.test1_2019;
drop table if exists public.test1_old2;
drop table if exists public.test1;
drop table if exists public.test1_default;
drop table if exists public.test2;
create table public.test1 (col1 text not null,col2 timestamp not null, col3
text,primary key (col1,col2)) partition by range (col2);
create table public.test2(col1 text not null,col2 timestamp not null,primary
key (col1));
alter table public.test1 add constraint test2_fkey foreign key (col3)
references public.test2(col1);
create table public.test1_default partition of public.test1 DEFAULT;
create table public.test1_2019 partition of public.test1 for values from
('2019-01-01') to ('2020-01-01');
alter table public.test1 rename to test1_old;
alter table public.test1_old detach partition public.test1_default;
alter table public.test1_old detach partition public.test1_2019;
alter table public.test1_default rename to test1;
alter table public.test1_2019 inherit public.test1;
drop table if exists public.test1_old;
alter table public.test1 rename to test1_old2;
alter table public.test1_2019 drop constraint test2_fkey;
alter table public.test1_old2 drop constraint test2_fkey;

The error produced is :
ERROR:  constraint "test2_fkey" of relation "test1_2019" does not exist

Alternatively, the second last statement (alter table public.test1_2019 drop
constraint test2_fkey;) can be left out, but then the following error is
produced: 
ERROR:  inherited constraint is not a CHECK constraint

The current workaround for this is to update pg_constraint directly :
update pg_constraint set connoinherit = true where conname  = 'test2_fkey'
and conrelid = 'public.test1_old2'::regclass::oid and contype = 'f';

Then the last statements works:
=# alter table public.test1_old2 drop constraint test2_fkey;
ALTER TABLE