From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Gustafsson <daniel@yesql.se>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "zedaardv@drizzle.com" <zedaardv@drizzle.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17720: pg_dump creates a dump with primary key that cannot
 be restored, when specifying 'using index ...'
In-reply-to: <9C039DE3-EFBD-4D65-B4CF-90D8148B7B4C@yesql.se>
References: <17720-dab8ee0fa85d316d@postgresql.org>
 <CAKFQuwam=1NoSNH=EJRUnuuD8JE_jM8tq22-SYqO1nxzoXFwzg@mail.gmail.com>
 <9C039DE3-EFBD-4D65-B4CF-90D8148B7B4C@yesql.se>
Comments: In-reply-to Daniel Gustafsson <daniel@yesql.se>
	message dated "Wed, 14 Dec 2022 14:02:40 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2684950.1671032222.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 14 Dec 2022 10:37:02 -0500
Message-ID: <2684951.1671032222@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2684951.1671032222%40sss.pgh.pa.us>
Precedence: bulk

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 yo=
u did here - a PK cannot contain nulls in any of its columns so indeed cho=
osing an index that specifies how nulls behave is non-sensical.  That said=
, it also doesn=E2=80=99t hurt so long as the column itself is indeed not =
null.  But extending the syntax doesn=E2=80=99t seem that appealing.

> Even if we prohibit this, there is still the case of all existing system=
s which
> can't be dumped.  I wonder if the solution is to teach pg_dump to not cr=
eate
> NULLS NOT DISTINCT primary key constraints?  The simple attached fix cre=
ates 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.

			regards, tom lane