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@sss.pgh.pa.us> wrote:
>
> Daniel Gustafsson <daniel@yesql.se> writes:
>> On 14 Dec 2022, at 13:54, David G. Johnston <david.g.johnston@gmail.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
anyof its columns so indeed choosing an index that specifies how nulls behave is non-sensical.  That said, it also
doesn’thurt 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.

Agreed, I'll expand the patch.

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