Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-08-23T18:00:13Z
Lists: pgsql-hackers 
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> If this is OK, we should make this API quirkiness very explicit in the
> comments, so the patch needs to be a few lines larger in order to be
> committable.  Also, perhaps the check should be that contype equals
> either primary or unique, rather than it doesn't equal foreign.

Yeah.  See lsyscache.c's get_constraint_index(), as well as commit
641f3dffc which fixed a mighty similar-seeming bug.  One question
that precedent raises is whether to also include CONSTRAINT_EXCLUSION.
But in any case a positive test for the constraint types to allow
seems best.

			regards, tom lane

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