Re: partitioned tables referenced by FKs

On 2019-Mar-18, Alvaro Herrera wrote:

> A pretty silly bug remains here. Watch:
>
> create table pk (a int primary key) partition by list (a);
> create table pk1 partition of pk for values in (1);
> create table fk (a int references pk);
> insert into pk values (1);
> insert into fk values (1);
> drop table pk cascade;
>
> Note that the DROP of the main PK table is pretty aggressive: since it
> cascades, you want it to drop the constraint on the FK side.

I ended up revising the dependencies that we give to the constraint in
the partition -- instead of giving it partition-type dependencies, we
give it an INTERNAL dependency. Now when you request to drop the
partition, it says this:

create table pk (a int primary key) partition by list (a);
create table fk (a int references pk);
create table pk1 partition of pk for values in (1);

alvherre=# drop table pk1;
ERROR: cannot drop table pk1 because other objects depend on it
DETAIL: constraint fk_a_fkey on table fk depends on table pk1
HINT: Use DROP ... CASCADE to drop the dependent objects too.

If you do say CASCADE, the constraint is dropped. Not really ideal (I
would prefer that the drop is prevented completely), but at least it's
not completely bogus. If you do "DROP TABLE pk", it works sanely.
Also, if you DETACH the partition that pg_depend row goes away, so a
subsequent drop of the partition works sanely.

Fixed the psql issue pointed out by Amit L too.

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