On Friday, May 31, 2024, Tom Lane <t...@sss.pgh.pa.us> wrote: > Kaiting Chen <ktche...@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.
