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

On Thu, Jul 23, 2015 at 5:47 AM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> 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?"

Sure. This does not seem to be complicated.

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

Yeah, I think we should be able to define a collation in this case.
For example it is as well possible to pass a WITH clause with storage
parameters, though we do not document it in
table_constraint_using_index
(http://www.postgresql.org/docs/devel/static/sql-altertable.html).

> +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.

Er, pushing such a patch on back-branches may break applications that
do exactly what Robert did in his test case (using a unique index as
primary key with a collation), no? I am not sure that this is
acceptable. Taking the problem at its root by swtiching pg_dump to
define an index and then use it looks a more solid approach on back
branches.

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

Yep. Definitely.
--
Michael