Re: [BUG] parenting a PK constraint to a self-FK one (Was: Self FK oddity when attaching a partition)

On 2022-Oct-05, Alvaro Herrera wrote:

> Backpatching this to 12 shows yet another problem -- the topmost
> relation acquires additional FK constraints, not yet sure why. I think
> we must have fixed something in 13 that wasn't backpatched, but I can't
> remember what it is and whether it was intentionally not backpatched.

This was actually a mismerge. Once I fixed that, it worked properly.

However, there was another bug, which only showed up when I did a
DETACH, ATTACH, and repeat. The problem is that when we detach, the
no-longer-partition retains an FK constraint to the partitioned table.
This is good -- we want that one -- but when we reattach, then we see
that the partitioned table is being referenced from outside, so we
consider that another constraint that we need to add the partition to,
*in addition to the constraint that we need to clone*. So we need to
ignore both a self-referencing FK that goes to the partitioned table, as
well as a self-referencing one that comes from the partition-to-be.
When we do that, then the clone correctly uses that one as the
constraint to retain and attach into the hierarchy of constraints, and
everything [appears to] work correctly.

So I've pushed this, and things are now mostly good. Two problems
remain, though I don't think either of them is terribly serious:

1. one of the constraints in the final hierarchy is marked as not
validated. I mentioned this before.

2. (only in 15) There are useless pg_depend rows for the pg_trigger
rows, which make them depend on their parent pg_trigger rows. This is
not related to self-referencing foreign keys, but I just happened to
notice because I was examining the catalog contents with the added test
case. I think this breakage is due to f4566345cf40. I couldn't find
any actual misbehavior caused by these extra pg_depend entries, but we
should not be creating them anyway.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Por suerte hoy explotó el califont porque si no me habría muerto
de aburrido" (Papelucho)