Re: Dump/Restore of non-default PKs

On Mon, Apr 18, 2022 at 1:48 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Mon, Apr 18, 2022 at 1:00 PM Simon Riggs <
> simon(dot)riggs(at)enterprisedb(dot)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.

David J.