From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | svb007(at)gmail(dot)com |
Subject: | BUG #15792: Dependency remains on foreign key constraint when child detached from declarative partition. |
Date: | 2019-05-07 00:51:04 |
Message-ID: | 15792-1e6a77de248fd6cd@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15792
Logged by: Stefan van der Berg
Email address: svb007(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-05-07 03:34:22 | Re: BUG #15745: WAL References Invalid Pages...that eventually resolves |
Previous Message | James Tomson | 2019-05-06 23:04:18 | invalid memory alloc request size from pg_stat_activity? |