Re: ALTER TABLE .. ADD PRIMARY KEY .. USING INDEX has dump-restore hazard

On Tue, Jul 21, 2015 at 8:34 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Wed, Jul 22, 2015 at 1:23 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Notice that the collation specifier is gone. Oops.
>
> As it is not possible to specify directly a constraint for a PRIMARY
> KEY expression, what about switching dumpConstraint to have it use
> first a CREATE INDEX query with the collation and then use ALTER TABLE
> to attach the constraint to it? I am noticing that we already fetch
> the index definition in indxinfo via pg_get_indexdef. Thoughts?

I guess the questions on my mind is "Why is it that you can't do this
directly when creating a primary key, but you can do it when turning
an existing index into a primary key?"

If there's a good reason for prohibiting doing this when creating a
primary key, then presumably it shouldn't be allowed when turning an
index into a primary key either. If there's not, then why not extend
the PRIMARY KEY syntax to allow it?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company