Vik Fearing <vik@postgresfriends.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. Do you expect the system to silently fix it for you if the index is on the wrong columns?
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.
No matter what we do we are either masking the situation that a user has a primary key backing index defined NULLS NOT DISTINCT which is a pointless, but not invalid, definition, or causing an error to happen because we've decided that we shouldn't be masking that inconsistent configuration.
Green-field, maybe we should have made it a "NULLS { NOT ALLOWED | [NOT] DISTINCT }" option and been better at informing/prohibiting the user from choosing an index that is not compatible with the constraint they want it to support. But that isn't really something we make an effort to do generally and it seems too late to get on that horse now.
Sleeping on this, I feel even more strongly that touching pg_dump now is wrong. The configuration, while nonsensical, is technically valid. Which means that the argument for causing existing pg_dumps to become broken is basically zero. We need to simply synchronize PRIMARY KEY to accept this configuration in the interest of dump/restore preservation.
