Re: partitioned tables referenced by FKs

On 2019-Mar-29, Robert Haas wrote:

> On Wed, Mar 20, 2019 at 11:58 AM Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > 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 don't know why dependency.c doesn't handle this internally. If I
> say that I want to delete a list of objects, some of which can't be
> dropped without dropping certain other things, dependency.c ought to
> be able to suspend judgement on what the problems are until it's
> looked over that whole list. It seems to me that we've had to fight
> with this problem before, and I don't know why every individual bit of
> code that calls into that file has to be responsible for working
> around it individually.

Hmm, if you think this kind of situation is more widespread than this
particular case, then maybe we can add the hooks I was talking about,
and simplify those other cases while fixing this problem. Do you have
it in your mind what cases are those? Nothing comes to mind, but I'll
search around and see if I can find anything.

Note that there are two separate problems here. One is how to run
arbitrary code to see whether the DROP is allowed (in this case, the
arbitrary code to run is RI_Final_Check which searches for referenced
rows and throws ERROR if there are any). My proposed hook system seems
to work OK for that.

The other problem is how to decide (in dependency.c terms) that the
partition can be dropped. We have these scenarios to support:

1. ALTER TABLE fk DROP CONSTRAINT -- should work cleanly
2. DROP referenced-partition -- allow only if no referenced rows
3. ALTER referenced-parent-table DETACH partition -- allow only if no referenced rows
4. DROP referenced-parent-table CASCADE -- drop the FK and partition

The patch I posted in v8 allowed cases 1, 2 and 3 but failed 4.

One possibility is to pass the whole list of objects to drop to the
hooks; so if we see (in the pg_class-specific hook) that the FK
constraint is listed as being dropped, we just don't execute
RI_Final_Check.

BTW, RI_Final_Check looks a bit like a silly name, now that I think
about it; it was only a temporary name I picked by opposition to
RI_Initial_Check. Maybe RI_PartitionRemove_Check would be more apropos?

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