On Friday, May 31, 2024, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
Kaiting Chen <ktchen14@gmail.com> writes:
> 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.
> ...
> 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.
I agree that there's a hazard there, but I question if the case is
sufficiently real-world to justify the costs of introducing a
non-SQL-standard clause in foreign key constraints.
One such cost is that pg_dump output would become less able to be
loaded into other DBMSes, or even into older PG versions.
I also wonder if this wouldn't just trade one fragility for another.
Specifically, I am not sure that we guarantee that the names of
indexes underlying constraints remain the same across dump/reload.
If they don't, the USING INDEX clause might fail unnecessarily.
As against that, I'm not sure I've ever seen a real-world case with
intentionally-duplicate unique indexes.
So on the whole I'm unconvinced that this is worth changing.
Seems like most of those issues could be avoided if we only supply “alter table” syntax (or a function…). i.e., give the dba a tool to modify their system when our default choices fail them. But continue on with the defaults as they exist today.
David J.