Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1p5qZM-0007qp-0J
	for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Dec 2022 15:54:48 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1p5qZK-0006EM-OQ
	for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Dec 2022 15:54:46 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1p5qZK-0006ED-GF
	for pgsql-bugs@lists.postgresql.org; Thu, 15 Dec 2022 15:54:46 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1p5qZI-00028d-FA
	for pgsql-bugs@lists.postgresql.org; Thu, 15 Dec 2022 15:54:45 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 2BFFscOj3371541;
	Thu, 15 Dec 2022 10:54:38 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
cc: Daniel Gustafsson <daniel@yesql.se>,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
        "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: <9344998b-e9aa-fdd8-9a7e-3f17811599fa@postgresfriends.org>
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>
Comments: In-reply-to Vik Fearing <vik@postgresfriends.org>
	message dated "Thu, 15 Dec 2022 16:30:36 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3371539.1671119678.1@sss.pgh.pa.us>
Date: Thu, 15 Dec 2022 10:54:38 -0500
Message-ID: <3371540.1671119678@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/3371540.1671119678%40sss.pgh.pa.us>
Precedence: bulk

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.
Do you expect the system to silently fix it for you if the index is
on the wrong columns?

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.

			regards, tom lane