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-23T16:30:06Z
Lists: pgsql-hackers

Attachments

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

Hi,

[...]

> However, it seems get_relation_idx_constraint_oid(), introduced in eb7ed3f3063,
> assume there could be only ONE constraint depending to an index. But in fact,
> multiple constraints can rely on the same index, eg.: the PK and a self
> referencing FK. In consequence, when looking for a constraint depending on an
> index for the given relation, either the FK or a PK can appears first depending
> on various conditions. It is then possible to trick it make a FK constraint a
> parent of a PK...

Hmm, wow, that sounds extremely stupid.  I think a sufficient fix might
be to have get_relation_idx_constraint_oid ignore any constraints that
are not unique or primary keys.  I tried your scenario with the attached
and it seems to work correctly.  Can you confirm?  (I only ran the
pg_regress tests, not anything else for now.)

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.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

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