Thread: 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

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

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

On 2019/01/24 12:03, Alvaro Herrera wrote:
> 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.

Thank you.

Regards,
Amit