Horton

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

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-08-24T10:49:13Z
Lists: pgsql-hackers 
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)

Commits

  1. Create FKs properly when attaching table as partition

  2. Fix self-referencing foreign keys with partitioned tables

  3. Fix GetForeignKey*Triggers for self-referential FKs

  4. Choose FK name correctly during partition attachment

  5. Update SQL features

  6. Restore the previous semantics of get_constraint_index().