Explicit specification of index ensuring uniqueness of foreign columns - Mailing list pgsql-hackers

I'd like to resurrect a subset of my proposal in [1], specifically that:

  The FOREIGN KEY constraint syntax gains a [ USING INDEX index_name ] clause
  optionally following the referenced column list.

  The index specified by this clause is used to support the foreign key
  constraint, and it must be a non-deferrable unique or primary key index on the
  referenced table compatible with the referenced columns.

I believe that it may be independently valuable to have some syntax available to
influence which index is used to ensure uniqueness of the foreign columns in a
foreign key constraint. Currently, this index is identified implicitly from the
REFERENCEd columns when the constraint is created. This causes the following to
imperfectly round trip through a pg_dump and restore:

  CREATE TABLE foo (
    id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
  );

  CREATE UNIQUE INDEX foo_key2 ON foo(id);
  CREATE UNIQUE INDEX foo_key1 ON foo(id);

  CREATE TABLE bar (
    foo_id INTEGER NOT NULL CONSTRAINT bar_fkey REFERENCES foo(id)
  );

Using this query to identify the unique index backing the bar_fkey constraint:

  SELECT objid, refobjid::regclass FROM pg_depend
  WHERE objid = (
    SELECT oid FROM pg_constraint WHERE conname = 'bar_fkey'
  ) AND refobjsubid = 0;

Then after the DDL is applied, the foreign key constraint depends on foo_key2:

  objid | refobjid
  -------+----------
  17152 | foo_key2

But following a pg_dump and restore, the foreign key's unique index dependency
has changed to foo_key1:

  objid | refobjid
  -------+----------
  17167 | foo_key1

This discrepancy appears to be caused by this confluence of circumstances:

1. The unique index backing the foreign side of a foreign key constraint is
   searched for in OID order:

   static Oid
   transformFkeyCheckAttrs(Relation pkrel,
                                                   int numattrs, int16 *attnums,
                                                   Oid *opclasses) /*
output parameter */
   {
           ...
           indexoidlist = RelationGetIndexList(pkrel);

           foreach(indexoidscan, indexoidlist)
           {
           ...
   }

2. The indexes appear in the pg_dump output before the FOREIGN KEY constraint,
   and they appear in lexicographic, rather than OID, order.

While, in this minimal reproduction, the two indexes are interchangeable, there
are situations that may reasonably occur in the course of ordinary use in which
they aren't. For example, a redundant unique index with different storage
parameters may exist during the migration of an application schema. If the
incorrect index is then selected to be a dependency of a foreign key constraint
following a pg_dump and restore, it will likely cause subsequent steps in the
migration to fail.

Note that this proposal deals with indexes rather than constraints because this
is, internally, what PostgreSQL uses. Specifically, PostgreSQL doesn’t actually
require there to be a unique constraint on the foreign columns of a foreign key
constraint; a unique index alone is sufficient. However, I believe that this
proposal would be essentially the same if it were changed to a USING CONSTRAINT
clause, since it is already possible to explicitly specify the underlying index
for a unique or primary key constraint.

If I submitted a patch implementing this functionality, would there be
any interest in it?

[1]:
https://www.postgresql.org/message-id/flat/CA%2BCLzG8HZUk8Gb9BKN88fgdSEqHx%3D2iq5aDdvbz7JqSFjA2WxA%40mail.gmail.com