Re: partitioned tables referenced by FKs

On 2019-Mar-19, Alvaro Herrera wrote:

> I think this is fixable by using a two-step strategy where we first
> compile a list of constraints being dropped, and in the second step we
> know to ignore those when checking partitions that are being dropped.
> Now, maybe the order of objects visited guarantees that the constraint
> is seen (by drop) before each corresponding partition, and in that case
> we don't need two steps, just one. I'll have a look at that.

I tried this, it's pretty horrible. I think it's not completely
impossible, but seems to require a "suppression list" of constraints,
that is, a list of constraints that ought not to be checked because
they're also being dropped in the same command. Here's what ugly: when
you drop a partition, its part of the FK constraint (the corresponding
pg_constraint row) is also dropped, but its parent constraint is what
remains. We need to know to suppress the error when the *parent
constraint* is dropped, but *not* suppress the error when only the child
constraint is dropped. I can only think of ugly data structure to
support this, and adding enough hooks in dependency.c to support this is
going to be badly received.

I think a better idea is to prevent DROP of a partition that's
referenced by a foreign key, period. We would only allow ALTER..DETACH
(they can drop the partition once it's detached). It seems to me that
the check can be done more naturally during detach than during drop,
because we're not constrained to do it within the dependency.c system.

However, this still doesn't solve the "DROP TABLE pk CASCADE" problem,
unless we register the dependencies differently. I've been trying to
understand how to make that work.

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