Re: Dump/Restore of non-default PKs

On Mon, 18 Apr 2022 at 21:48, 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.

Sigh, agreed. It's more work, but its cleaner in the longer term to
separate indexes from constraints.

I'll look in more detail and come back here later.

Thanks both.

--
Simon Riggs http://www.EnterpriseDB.com/