[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Table partition with primary key in 11.3
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2019-05-22 17:57:40
Message-ID: 6831.1558547860 () sss ! pgh ! pa ! us
[Download RAW message or body]

User <ya@penek.org> writes:
> 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@alvh.no-ip.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


[prev in list] [next in list] [prev in thread] [next in thread]