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

On Thu, Dec 15, 2022 at 8:54 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
> 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.

David J.