From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: pgsql: Allow UNIQUE indexes on partitioned tables |
Date: | 2018-02-20 20:49:42 |
Message-ID: | CAKFQuwZ1hjrNt6AKT88xcObzoZN6gasE4gmXOtduQAwN-HY-HA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers pgsql-hackers |
On Tue, Feb 20, 2018 at 8:36 AM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:
> 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.
>
[...]
> 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.
>
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.
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);
>
> I have trouble coming up with a real-world example where you would run
> into this limitation in practice.
>
Indeed
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2018-02-20 22:58:44 | pgsql: Error message improvement |
Previous Message | Tom Lane | 2018-02-20 16:23:52 | pgsql: Fix pg_dump's logic for eliding sequence limits that match the d |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2018-02-20 21:07:13 | Re: [HACKERS] Transactions involving multiple postgres foreign servers |
Previous Message | Andres Freund | 2018-02-20 20:36:16 | Re: ALTER TABLE ADD COLUMN fast default |