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