Re: Postgres 11: Table Partitioning and Primary Keys

On 2019-Jul-09, Amit Langote wrote:

> As mentioned in the docs, defining exclusion constraints on
> partitioned tables is not supported.

Right.

> "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? Also, I chose to place it just above the existing para
that mentions FK limitations, which reads:

<listitem>
<para>
While primary keys are supported on partitioned tables, foreign
keys referencing partitioned tables are not supported. (Foreign key
references from a partitioned table to some other table are supported.)
</para>

Your proposed wording seemed to use too many of the same words, which
prompted me to change a bit. Maybe I read too many novels and
insufficient technical literature.

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?

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services