Re: Postgres 11: Table Partitioning and Primary Keys

On Tue, Jul 9, 2019 at 8:29 AM Michael Paquier <[email protected]> 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
>