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

On Wed, Jul 22, 2015 at 7:34 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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?
>

+1. I think in the short term we can treat this as a bug, and "fix" the bug
by disallowing an index with attributes that cannot be present in an index
created by PRIMARY KEY constraint. The collation attribute on one of the
keys may be just one of many such attributes.

In the long term, we may want to allow collation in primary key, but that
will be a feature ideally suited for a major version release.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/