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: Zhihong Yu <zyu@yugabyte.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-10-05T10:55:23Z
Lists: pgsql-hackers 
On 2022-Oct-03, Jehan-Guillaume de Rorthais wrote:

> Thank you! This is fixed and rebased on current master branch in patches
> attached.

Thanks.  As far as I can see this fixes the bugs that were reported.
I've been giving the patches a look and it caused me to notice two
additional bugs in the same area:

- FKs in partitions are sometimes marked NOT VALID.  This is because of
  missing initialization when faking up a Constraint node in
  CloneFkReferencing.  Easy to fix, have patch, running tests now.

- The feature added by d6f96ed94e73 (ON DELETE SET NULL (...)) is not
  correctly propagated.  This should be an easy fix also, haven't tried,
  need to add a test case.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

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().