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

On 2022-Aug-24, Jehan-Guillaume de Rorthais wrote:

> I was naively wondering about such a patch, but was worrying about potential
> side effects on ATExecAttachPartitionIdx(), DetachPartitionFinalize() and
> DefineIndex() where I didn't had a single glance. Did you had a look?

No. But AFAIR all the code there is supposed to worry about unique
constraints and PK only, not FKs. So if something changes, then most
likely it was wrong to begin with.

> I did a quick ATTACH + DETACH test, and it seems DETACH partly fails with its
> housecleaning:

Ugh. More fixes required, then.

> Looking for few minutes in ATExecDetachPartitionFinalize(), it seems it only
> support removing the parental link on FK, not to clean the FKs added during the
> ATTACH DDL anyway. That explains the FK child1->parent left behind. But in
> fact, this let me wonder if this part of the code ever considered implication
> of self-FK during the ATTACH and DETACH process?

No, or at least I don't remember thinking about self-referencing FKs.
If there are no tests for it, then that's likely what happened.

> Why in the first place TWO FK are created during the ATTACH DDL?

That's probably a bug too.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"The eagle never lost so much time, as
when he submitted to learn of the crow." (William Blake)