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

> On 14 Dec 2022, at 16:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Daniel Gustafsson <daniel(at)yesql(dot)se> writes:
>> On 14 Dec 2022, at 13:54, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>> There is a decent chance that the fix here is to prohibit doing what you did here - a PK cannot contain nulls in any of its columns so indeed choosing an index that specifies how nulls behave is non-sensical. That said, it also doesn’t hurt so long as the column itself is indeed not null. But extending the syntax doesn’t seem that appealing.
>
>> Even if we prohibit this, there is still the case of all existing systems which
>> can't be dumped. I wonder if the solution is to teach pg_dump to not create
>> NULLS NOT DISTINCT primary key constraints? The simple attached fix creates a
>> valid PK constraint on the above schema.
>
> It doesn't make sense for pg_dump to editorialize on a schema that
> we otherwise consider valid; people would rightfully complain that
> dump/restore changed things. So we need to do both things: prohibit
> adopting such an index as a PK constraint (but I guess it's okay
> for plain unique constraints?), and adjust pg_dump to compensate
> for the legacy case where it was already done.

The attached prohibits the use of NULLS NOT DISTINCT for backing primary key
constraints but allow them for unique constraints. Is this along the lines of
what you had in mind?

--
Daniel Gustafsson https://vmware.com/