inherited primary key misbehavior
Hi,
It seems that ATExecDetachPartition misses to mark a child's primary key
constraint entry (if any) as local (conislocal=true, coninhcount=0), which
causes this:
create table p (a int primary key) partition by list (a);
create table p2 partition of p for values in (2);
alter table p detach partition p2;
alter table p2 drop constraint p2_pkey ;
ERROR: cannot drop inherited constraint "p2_pkey" of relation "p2"
select conname, conislocal, coninhcount from pg_constraint where conrelid
= 'p2'::regclass;
conname │ conislocal │ coninhcount
─────────┼────────────┼─────────────
p2_pkey │ f │ 1
(1 row)
How about the attached patch?
Thanks,
Amit
Attachments:
0001-Detach-primary-key-constraint-when-detaching-partiti.patchtext/plain; charset=UTF-8; name=0001-Detach-primary-key-constraint-when-detaching-partiti.patchDownload+29-1
Hello
On 2019-Jan-23, Amit Langote wrote:
It seems that ATExecDetachPartition misses to mark a child's primary key
constraint entry (if any) as local (conislocal=true, coninhcount=0), which
causes this:create table p (a int primary key) partition by list (a);
create table p2 partition of p for values in (2);
alter table p detach partition p2;
alter table p2 drop constraint p2_pkey ;
ERROR: cannot drop inherited constraint "p2_pkey" of relation "p2"
Ugh.
I suppose unique keys would have the same problem, so the check for
indisprimary is wrong. Other than that, looks correct.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2019/01/24 6:10, Alvaro Herrera wrote:
Hello
On 2019-Jan-23, Amit Langote wrote:
It seems that ATExecDetachPartition misses to mark a child's primary key
constraint entry (if any) as local (conislocal=true, coninhcount=0), which
causes this:create table p (a int primary key) partition by list (a);
create table p2 partition of p for values in (2);
alter table p detach partition p2;
alter table p2 drop constraint p2_pkey ;
ERROR: cannot drop inherited constraint "p2_pkey" of relation "p2"Ugh.
I suppose unique keys would have the same problem, so the check for
indisprimary is wrong.
Fixed in the attached. We don't support inheriting EXCLUSION constraints
yet, so no need to consider their indexes.
Thanks,
Amit
Attachments:
v2-0001-Detach-primary-key-constraint-when-detaching-part.patchtext/plain; charset=UTF-8; name=v2-0001-Detach-primary-key-constraint-when-detaching-part.patchDownload+45-1
On 2019-Jan-24, Amit Langote wrote:
Fixed in the attached. We don't support inheriting EXCLUSION constraints
yet, so no need to consider their indexes.
Looks good, pushed.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services