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

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [BUG] parenting a PK constraint to a self-FK one (Was: Self FK oddity when attaching a partition)
Date: 2022-08-23 16:30:06
Message-ID: 20220823163006.smdpovyl4dhcakvr@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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



> 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 —

Attachment Content-Type Size
0001-test-fix.patch text/x-diff 908 bytes

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nikita Glukhov 2022-08-23 16:36:11 Re: SQL/JSON features for v15
Previous Message Jacob Champion 2022-08-23 16:27:18 Re: CFM Manager