Thread: 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

David G. Johnston wrote:

> Something like:
> 
> When establishing a unique constraint for a multi-level partition hierarchy
> all the "partition by" columns of the target partitioned table, as well as
> those of all its descendant partitioned tables, must be included in the
> constraint definition.

Yeah, that seems better to me.  Pushed.

> If I understand the above then the following failing test would be a worthy
> addition to memorialize the behavior of ALTER TABLE ATTACH under this
> constraint.
> 
> create table idxpart (a int primary key, b int) partition by range (a);
> create table idxpart1 (a int not null, b int, primary key (a, b)) partition
> by range (a, b);
> alter table idxpart attach partition idxpart1 for values from (1) to (1000);

Included this one too.


Thanks for reading!

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

On 2018-Feb-19, David G. Johnston wrote:

> As an aside, adding a link to "Data Definiton/Table Partitioning" from at
> least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and
> "PARTITION OF" in the Parameters section of that page - one must partition
> by a table before one can partition it (and also the synopsis lists them in
> the BY before OF order), would be helpful.

A little late, I have done these two changes.

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

On 2018/07/17 13:57, Alvaro Herrera wrote:
> On 2018-Feb-19, David G. Johnston wrote:
> 
>> As an aside, adding a link to "Data Definiton/Table Partitioning" from at
>> least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and
>> "PARTITION OF" in the Parameters section of that page - one must partition
>> by a table before one can partition it (and also the synopsis lists them in
>> the BY before OF order), would be helpful.
> 
> A little late, I have done these two changes.

A belated +1.

Thanks,
Amit