Re: pgsql: Allow UNIQUE indexes on partitioned tables

Many thanks for reading through it!

David G. Johnston wrote:
> I found the following change to be confusing.
[...]
> I was expecting the doc for ADD CONSTRAINT USING INDEX to note the
> limitation explicitly - in lieu of the above paragraph.

Agreed. I moved the note to ADD CONSTRAINT and added a different on to
ADD CONSTRAINT USING INDEX.

> Also, I cannot reason out what the following limitation means:
>
> /doc/src/sgml/ref/create_table.sgml
> + If any partitions are in turn partitioned, all columns of each
> partition
> + key are considered at each level below the <literal>UNIQUE</literal>
> + constraint.

I can see that being unclear. I tried to be very concise, to avoid
spending too many words on what is mostly a fringe feature; but that
probably didn't work very well. Wording suggestions welcome. What this
means is that if you create a partition that is partitioned on a column
different from its parent, then a primary key that covers the whole
hierarchy (i.e. you're not just adding a PK to the partitioned
partition) must include all partition columns, not just the upper one.

Example:

create table t (a int, b int) partition by range (a);
create table t_1 partition of t for values from (0) to (1000) partition by range (b);

then you may create a unique or PK constraint on t only if you include
both columns (a,b). You may not create a PK on t (a), which is a bit
surprising since (b) is not part of the partition key of t directly,
only of t_1.

Of course, if you create a unique constraint on t_1 (i.e. it doesn't
cover all of t) then you may use (b) alone -- that's what "each level
below the UNIQUE constraint" supposed to convey.

I have trouble coming up with a real-world example where you would run
into this limitation in practice.

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