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

On Wed, Dec 14, 2022 at 8:37 AM 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 WHERE clause of CREATE INDEX doesn't pose an issue as we report "Cannot
create a primary key or unique constraint using such an index".
It is also not possible to specify an opclass in PRIMARY KEY, but since we
document that unique indexes are only currently implemented by B-tree, and
that is what you get from PRIMARY KEY, that is also not a problem
(presently - unless extension authors were to bypass this).

The remaining elements: INCLUDE, WITH, and TABLESPACE, all exist in both
formulations.

I am thinking now that the failure to include NULLS [NOT[ DISTINCT in the
CREATE TABLE syntax is an oversight that needs to be fixed. It just
doesn't make sense to have the two commands expose different features.

David J.