"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Mon, Apr 18, 2022 at 1:00 PM Simon Riggs <simon.riggs@enterprisedb.com> > wrote: >> I propose that we change pg_dump so that when it creates a PK it does >> so in 2 commands: >> 1. CREATE [UNIQUE] INDEX iname ... >> 2. ALTER TABLE .. ADD PRIMARY KEY USING INDEX iname;
> Why not just get rid of the limitation that constraint definitions don't > support non-default methods?
That approach would be doubling down on the assumption that we can always shoehorn more custom options into SQL-standard constraint clauses, and we'll never fall foul of shift/reduce problems or future spec additions. I think for example that USING INDEX TABLESPACE is a blot on humanity, and I'd be very glad to see pg_dump stop using it in favor of doing things as Simon suggests.
I'm convinced.
As for portability - that would be something we could explicitly define and support through a pg_dump option. In compatibility mode you get whatever the default index would be for your engine while by default we output the existing index as defined and then alter-add it to the table.
