MIME-Version: 1.0
References: <CANbhV-GdnAc-fpUvtkwJ5+YGoKYVOmTUQp9k1A4ODo=bdJ-Oww@mail.gmail.com>
 <CAKFQuwbZcudqgSCwM4rKoOfs1AbjN3ssPXv6yqmABGitk2CBwg@mail.gmail.com>
 <2379840.1650314887@sss.pgh.pa.us> <62224288-cbc0-a759-4ff6-40baa32943eb@enterprisedb.com>
In-Reply-To: <62224288-cbc0-a759-4ff6-40baa32943eb@enterprisedb.com>
From: Simon Riggs <simon.riggs@enterprisedb.com>
Date: Thu, 21 Apr 2022 12:43:10 +0100
Message-ID: <CANbhV-HiCEnmvb4FKy6Xg5j2dXuJQMqrmDGxmqfpOPbaDmpUNQ@mail.gmail.com>
Subject: Re: Dump/Restore of non-default PKs
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CANbhV-HiCEnmvb4FKy6Xg5j2dXuJQMqrmDGxmqfpOPbaDmpUNQ%40mail.gmail.com>
Precedence: bulk

On Wed, 20 Apr 2022 at 21:46, Peter Eisentraut
<peter.eisentraut@enterprisedb.com> 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/