From: Vik Fearing <vik@postgresfriends.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
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: Thu, 15 Dec 2022 17:08:56 +0100
Message-ID: <61f17db7-9f9b-0ff8-3c0f-549f9592f883@postgresfriends.org> (raw)
In-Reply-To: <3371540.1671119678@sss.pgh.pa.us>
References: <17720-dab8ee0fa85d316d@postgresql.org>
<CAKFQuwam=1NoSNH=EJRUnuuD8JE_jM8tq22-SYqO1nxzoXFwzg@mail.gmail.com>
<9C039DE3-EFBD-4D65-B4CF-90D8148B7B4C@yesql.se>
<2684951.1671032222@sss.pgh.pa.us>
<20CD10FD-A9C2-47D0-9A75-B86532BC058E@yesql.se>
<3039077.1671060472@sss.pgh.pa.us>
<9566C00C-6A3B-4CC7-8C7C-1A0477A5C752@yesql.se>
<9344998b-e9aa-fdd8-9a7e-3f17811599fa@postgresfriends.org>
<3371540.1671119678@sss.pgh.pa.us>
On 12/15/22 16:54, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> If the point in adding a primary key USING INDEX is to avoid building an
>> index, then this restriction defeats that purpose. We have no ALTER
>> INDEX command to switch or drop the <unique null treatment>.
>
> Well, if you want to build an index to then use as a primary key,
> it's incumbent on you to make the index with the correct properties.
This is assuming you initially created the index for the purpose of
making it the primary key, perhaps for concurrency.
> Do you expect the system to silently fix it for you if the index is
> on the wrong columns?
Of course not.
> I might have more sympathy for this argument if the correct <unique null
> treatment> were non-default, but it is not. You'd have had to go out
> of your way to make the index incompatible. That in turn suggests that
> there's a mistake somewhere, so having the system automatically fix
> it for you might just be masking something that would be better dealt
> with manually.
The index may have been originally created for something else. The only
way to deal with this manually is to drop and recreate the index, just
to remove something that has no effect anyway because the index contains
no nulls.
I am not going to die on this hill, but I think it is a shame to make
users go through that. A NOTICE saying "btw, we did that" should be
sufficient.
--
Vik Fearing
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: vik@postgresfriends.org, tgl@sss.pgh.pa.us, daniel@yesql.se, peter.eisentraut@enterprisedb.com, 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: <61f17db7-9f9b-0ff8-3c0f-549f9592f883@postgresfriends.org>
* 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