Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT

On 2019-Jan-15, Tom Lane wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > On 2019-Jan-10, Jesper Pedersen wrote:
> >> However, when you use ADD CONSTRAINT FOREIGN KEY you can't use ONLY, so
> >> would it be a good idea to make ADD CONSTRAINT UNIQUE behave the same way,
> >> i.e. error out ?
>
> > I haven't investigated this angle. It seems more complex than just a
> > simple bugfix, right?
>
> Wouldn't that be throwing away the entire point of the ONLY behavior,
> ie to allow the component indexes to be built one at a time, without
> holding locks across the whole partition tree?

I now see that Jesper was talking about a completely different thing
than I was thinking. I agree with you there -- it makes no sense to
reject that command ... particularly because pg_dump uses it.

What was on my head ("can we add ONLY to ADD FOREIGN KEY?") was the idea
that it'd be useful to construct the foreign keys in partitions, one by
one, and as a final step you construct a foreign key in the partitioned
table and then attach each FK in partition to the master one. Right
now, adding the foreign key in the parent table just creates duplicates
in the partitions, which is silly.

create table foo (a int primary key);
create table barp (a int) partition by list (a);
create table barp1 partition of barp for values in (1);
create table barp2 partition of barp for values in (2);
alter table barp1 add foreign key (a) references foo;
alter table barp2 add foreign key (a) references foo;

At this point, the partitions have one FK each, and it would be neat to
merge them as a unit, creating a parent constraint. But if you do:
alter table barp add foreign key (a) references foo;
you end up with two FKs in each partition.

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