From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: zedaardv@drizzle.com <zedaardv@drizzle.com>
Cc: 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 ...'
Date: Wed, 14 Dec 2022 10:37:02 -0500
Message-ID: <2684951.1671032222@sss.pgh.pa.us> (raw)
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>
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 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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel@yesql.se, david.g.johnston@gmail.com, zedaardv@drizzle.com, 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: <2684951.1671032222@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox