Subject: Re: Dump/Restore of non-default PKs

From: Simon Riggs <[email-masked]>
Date: Thu, 21 Apr 2022 12:43:10 +0100

On Wed, 20 Apr 2022 at 21:46, Peter Eisentraut
<[email-masked]> wrote:
>
> On 18.04.22 22:48, Tom Lane wrote:
> >> 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.
>
> When we do get the ability to create a table with a primary key with an
> underlying hash index, how would that be done?  Would the only way be
>
> 1. create the table without primary key
> 2. create the index
> 3. attach the index as primary key constraint
>
> That doesn't sound attractive.

Can you explain what you find unattractive about it?

The alternative is we have this

1. create the table without primary key
2. attach the index as primary key constraint (which must be extended
to include ALL of the options available on create index)

Having to extend ALTER TABLE so it exactly matches CREATE INDEX is
painful and maintaining it that way seems unattractive, to me.

Just so we are clear this is not about hash indexes, this is about
using ANY kind of index (i.e. any index access method, extension or
otherwise) to enforce a constraint.

Another idea might be to allow some kind of statement embedding... so
we don't need to constantly fiddle with ALTER TABLE
ALTER TABLE foo ADD PRIMARY KEY USING INDEX (CREATE INDEX .... )

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

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 22 Apr 2022 15:38:35 +0200

> Can you explain what you find unattractive about it?

Well, if I want to create a table with a primary key, the established 
way is to say "primary key", not to have to assemble it from multiple 
pieces.

I think this case is very similar to exclusion constraints, which also 
have syntax to specify the index access method.

---

From: Tom Lane <[email-masked]>
Date: Fri, 22 Apr 2022 10:14:23 -0400

> have syntax to specify the index access method.

That analogy would be compelling if exclusion constraints were a
SQL-standard feature; but they aren't so their clause syntax is
fully under our control.  The scenario that worries me is that
somewhere down the pike, the SQL committee might extend the
syntax of PKEY/UNIQUE constraint clauses in a way that breaks
our nonstandard extensions of them.

However, independently of whether we offer a syntax option or not,
it may still simplify pg_dump to make it treat the constraint and
the index as independent objects in all cases.

			regards, tom lane

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 28 Apr 2022 16:09:18 +0200

> our nonstandard extensions of them.

Some syntax like

     PRIMARY KEY (x, y) USING ACCESS METHOD hash

should be able to avoid any future clashes.

---

From: Simon Riggs <[email-masked]>
Date: Mon, 1 Aug 2022 17:00:41 +0100

> should be able to avoid any future clashes.

That seems to conflict with USING INDEX TABLESPACE. I've tried a few
things but have not found anything yet.

Any other ideas on syntax?