Re: Postgres 11: Table Partitioning and Primary Keys

Sorry for jumping in late here.

On Tue, Jul 9, 2019 at 3:51 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> On Tue, Jul 09, 2019 at 03:34:48PM +0900, Michael Paquier wrote:
> > Looking closely at the code in DefineIndex() (and as Rajkumar has
> > mentioned upthread for unique constraints) this can happen for primary
> > keys, unique constraints and exclusion constraints. So we had better
> > mention all three of them. I am not sure that we need to be explicit
> > about the uniqueness part though, let's say the following:
> > "When defining a primary key, a unique constraint or an exclusion
> > constraint on a partitioned table, all the columns present in the
> > constraint definition must be included in the partition key."
>
> Let's try again that (that's a long day..):
> "When defining a primary key, a unique constraint or an exclusion
> constraint on a partitioned table, all the columns present in the
> partition key must be included in the constraint definition."

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

-- on 13dev
create table p (a int, exclude using gist (a with &&)) partition by list (a);
ERROR: exclusion constraints are not supported on partitioned tables

Regarding primary key and unique constraints, how about writing it
such that it's clear that there are limitations? Maybe like:

"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."

Maybe, as David also says, it might be a good idea to mention the
reason why. So maybe like:

"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."

Thanks,
Amit

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
5.11.2.3. Limitations
The following limitations apply to partitioned tables:
* There is no way to create an exclusion constraint spanning all
partitions; it is only possible to constrain each leaf partition
individually.