>> 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.ga4...@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 and another 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.
