Horton

Re: Postgres 11: Table Partitioning and Primary Keys

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Michael Paquier <michael@paquier.xyz>, "David G. Johnston" <david.g.johnston@gmail.com>, Bruce Momjian <bruce@momjian.us>, phil.bayer@gmail.com, pgsql-docs@lists.postgresql.org, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-07-10T05:30:15Z
Lists: pgsql-hackers, pgsql-docs 
On Wed, Jul 10, 2019 at 7:53 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Jul-09, Amit Langote wrote:
> > "While defining a primary key and unique constraints on partitioned
> > tables is supported, the set of columns being constrained must include
> > all of the partition key columns.  This limitation exists because
> > <productname>PostgreSQL</productname> can ensure uniqueness only
> > across a given partition."
>
> I feel that PKs are mostly a special case of UNIQUE keys, so I tend to
> mention UNIQUE as the central element and let PKs fall out from that.
> That's a mild personal preference only though.  Anyway, based on your
> proposed wording, I wrote this:
>
>      <listitem>
>       <para>
>        Unique constraints on partitioned tables (as well as primary keys)
>        must constrain all the partition key columns.  This limitation exists
>        because <productname>PostgreSQL</productname> can only enforce
>        uniqueness in each partition individually.
>       </para>
>      </listitem>
>
> I'm not really sure about the "must constrain" verbiage.  Is that really
> comprehensible?

Looks good after replacing "must constraint" by "must include" as
suggested by Tom.

> Also, I chose to place it just above the existing para
> that mentions FK limitations

This placement of the new text sounds good.

> In CREATE TABLE, we already have this:
>      <para>
>       When establishing a unique constraint for a multi-level partition
>       hierarchy, all the columns in the partition key of the target
>       partitioned table, as well as those of all its descendant partitioned
>       tables, must be included in the constraint definition.
>      </para>
>
> which may not be the pinnacle of clarity, but took some time to craft
> and I think is correct.  Also it doesn't mention primary keys
> explicitly; maybe we should patch it by adding "(as well as a primary
> key)" right after "a unique constraint".  Thoughts?

Works for me.

Thanks,
Amit

Commits

  1. Mention limitation of unique in partitioned tables