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

> On 14 Dec 2022, at 13:54, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Wednesday, December 14, 2022, PG Bug reporting form <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org>> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17720
> Logged by: reiner peterke
> Email address: zedaardv(at)drizzle(dot)com <mailto:zedaardv(at)drizzle(dot)com>
> PostgreSQL version: 15.1
> Operating system: openSUSE Leap 15.4
> Description:
>
> Do a pg_dump of the database.
> the dump creates the code for a primary key that cannot be restored
> pg_dump -p 5632 -Of tranquility.sql -d tranquility
> on restore, I get the following error
> psql:tranquility.sql:90: ERROR: syntax error at or near "NULLS"
> LINE 2: ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT...
> in the dump itself the create constraint command is
> ALTER TABLE ONLY moon.hamster
> ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT (under);
> which does not work with the NULLS NOT DISTINCT string
>
> 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.

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