Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'

On 12/15/22 16:54, Tom Lane wrote:
> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
>> If the point in adding a primary key USING INDEX is to avoid building an
>> index, then this restriction defeats that purpose. We have no ALTER
>> INDEX command to switch or drop the <unique null treatment>.
>
> Well, if you want to build an index to then use as a primary key,
> it's incumbent on you to make the index with the correct properties.

This is assuming you initially created the index for the purpose of
making it the primary key, perhaps for concurrency.

> Do you expect the system to silently fix it for you if the index is
> on the wrong columns?

Of course not.

> I might have more sympathy for this argument if the correct <unique null
> treatment> were non-default, but it is not. You'd have had to go out
> of your way to make the index incompatible. That in turn suggests that
> there's a mistake somewhere, so having the system automatically fix
> it for you might just be masking something that would be better dealt
> with manually.

The index may have been originally created for something else. The only
way to deal with this manually is to drop and recreate the index, just
to remove something that has no effect anyway because the index contains
no nulls.

I am not going to die on this hill, but I think it is a shame to make
users go through that. A NOTICE saying "btw, we did that" should be
sufficient.
--
Vik Fearing