Re: Table partition with primary key in 11.3 - Mailing list pgsql-general

From Alex V.
Subject Re: Table partition with primary key in 11.3
Date
Msg-id 1985070938.2403.1559817000895.JavaMail.zimbra@pclovers.ru
Whole thread Raw
In response to Table partition with primary key in 11.3  (User <ya@penek.org>)
Responses Re: Table partition with primary key in 11.3
List pgsql-general
>> CREATE TABLE public.test1 (
>>      x1 integer NOT NULL,
>>      x2 integer NOT NULL,
>>      CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
>> ) PARTITION BY RANGE (x2);

>> This query works in 11.1 but fails in 11.3 with messages:

>> ERROR: insufficient columns in PRIMARY KEY constraint definition
>> DETAIL: PRIMARY KEY constraint on table "test1" lacks column "x2" which 
>> is part of the partition key.
>> SQL state: 0A000

> Indeed, that primary key is no good.  It was a bug that 11.1
> allowed it, which was fixed here:

> Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> Branch: master [0ad41cf53] 2019-01-14 19:28:10 -0300
> Branch: REL_11_STABLE Release: REL_11_2 [74aa7e046] 2019-01-14 19:25:19 -0300

>     Fix unique INCLUDE indexes on partitioned tables
    
>     We were considering the INCLUDE columns as part of the key, allowing
>     unicity-violating rows to be inserted in different partitions.
    
>     Concurrent development conflict in eb7ed3f30634 and 8224de4f42cc.
    
>     Reported-by: Justin Pryzby
>     Discussion: https://postgr.es/m/20190109065109.GA4285@telsasoft.com

>             regards, tom lane

I think that your position about primary keys in partitional tables is not right.

If we see regular table, one-field primary key is cross-table unique.
In partitional tables for users view we MUST also seen unique one-field primary key because this is user requirement
andanother keys can destroy logic between regular and partitional tables and functionality of partitioning becomes
useless.
For administrators of table we not-MAY, but MUST see one-field unique primary key in cross-table realisation.
All another realizations are plugging holes in a hurry and non-logical at global review of engine.



pgsql-general by date:

Previous
From: Jakub Olczyk
Date:
Subject: Re: Postgresql-11 confusing situtation
Next
From: Steve Rogerson
Date:
Subject: postgres 11 issue?