Re: Postgres 11: Table Partitioning and Primary Keys

On Tue, Jul 9, 2019 at 8:29 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Mon, Jul 08, 2019 at 10:37:37PM -0400, Bruce Momjian wrote:
> > On Fri, Jul 5, 2019 at 09:20:07PM +0000, PG Doc comments form wrote:
> >> In the documentation for Postgres 11 table partitioning, there is no
> mention
> >> of the requirement that the Primary Key of a partitioned table must
> contain
> >> the partition key.
> >> In fact the documentation on primary keys is so light that I am not even
> >> 100% sure the above is correct. If the following table is not possible
> in
> >> Postgres 11, the documentation should find some way to make that
> clear.
> >>
> >> I believe this should be documented in section "5.10.2.3. Limitations"
> >
> > Can someone comment on this? CC to hackers.
>
> Yep, that's the case:
> =# CREATE TABLE parent_tab (id int, id2 int primary key)
> PARTITION BY RANGE (id);
> ERROR: 0A000: insufficient columns in PRIMARY KEY constraint
> definition
> DETAIL: PRIMARY KEY constraint on table "parent_tab" lacks column
> "id" which is part of the partition key.
> LOCATION: DefineIndex, indexcmds.c:894
>
same is valid for UNIQUE constraint also.

postgres=# CREATE TABLE parent_tab (id int, id2 int unique)
PARTITION BY RANGE (id);
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "parent_tab" lacks column "id" which is
part of the partition key.

>
> I agree with the report here that adding one sentence to 5.10.2.3
> which is for the limitations of declarative partitioning would be a
> good idea. We don't mention the limitation in CREATE TABLE either
> (which would be rather incorrect IMO).
>
> Attached is an idea of patch for the documentation, using this
> wording:
> + <listitem>
> + <para>
> + When defining a primary key on a partitioned table, the primary
> + key column must be included in the partition key.
> + </para>
> + </listitem>
> If somebody has any better idea for that paragraph, please feel free.
> --
> Michael
>