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 ) id 1p5qnG-0008Uv-So for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Dec 2022 16:09:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5qnE-0003bh-MK for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Dec 2022 16:09:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5qnE-0003bX-EN for pgsql-bugs@lists.postgresql.org; Thu, 15 Dec 2022 16:09:08 +0000 Received: from relay4-d.mail.gandi.net ([2001:4b98:dc4:8::224]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5qn8-0007EC-7G for pgsql-bugs@lists.postgresql.org; Thu, 15 Dec 2022 16:09:07 +0000 Received: (Authenticated sender: vik@postgresfriends.org) by mail.gandi.net (Postfix) with ESMTPSA id D2805E0014; Thu, 15 Dec 2022 16:08:57 +0000 (UTC) Message-ID: <61f17db7-9f9b-0ff8-3c0f-549f9592f883@postgresfriends.org> Date: Thu, 15 Dec 2022 17:08:56 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...' Content-Language: en-US To: Tom Lane Cc: Daniel Gustafsson , Peter Eisentraut , "David G. Johnston" , "zedaardv@drizzle.com" , "pgsql-bugs@lists.postgresql.org" References: <17720-dab8ee0fa85d316d@postgresql.org> <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> From: Vik Fearing In-Reply-To: <3371540.1671119678@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/15/22 16:54, Tom Lane wrote: > Vik Fearing 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 . > > 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 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