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 1pYWV7-0003rX-VO for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 18:20:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYWV5-00070b-72 for pgsql-general@arkaria.postgresql.org; Sat, 04 Mar 2023 18:20:55 +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 ) id 1pY6dn-0006Se-0Y for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 14:44:11 +0000 Received: from ms11p00im-qufo17281701.me.com ([17.58.38.54]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pY6dk-0006KS-Kq for pgsql-general@postgresql.org; Fri, 03 Mar 2023 14:44:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1677854646; bh=W8HvOIXJqVS2kuZtXlnhKyirRg1RIItto6Ym/Qa2Luw=; h=To:From:Subject:Date:Message-id:Content-Type:MIME-Version; b=KN9fpK3f95kIJe3rWNf0x6RX4WQrQVkd/pOU/JKGlk3tDo1484pJBnGnCarN1JF+h DY9nAUeCo8XthzzpXiywdXt2uVejB8BHqAWEncIkF6lTkS4cq0NS4MuRuh4JJO7UFw wIPhWoGNBVYS2NJN5m/FSi8+IZSvYMDmERqxj8r2abuclnck1O9G2yDClw/mSJOBSq +MgGHGIlK4SfBX8dVwyfXgmaGHqIoL1c7I2mcML6Z26VziW5rT3yNwXU19CCA683Tc F09Qm0ZH+jOlb9a2YHSOwBiGUP959Z2/cmUeRC+a6Lyd6rcfb5BRYiHOgH5NFn2DJQ DOxJ0NxqTtJjw== Received: from ms11p00im-qufo17281701.me.com (ms11p00im-qufo17281701.me.com [17.58.38.54]) by ms11p00im-qufo17281701.me.com (Postfix) with ESMTPS id 4CB0F7428EF; Fri, 3 Mar 2023 14:44:06 +0000 (UTC) Received: from localhost (ms11p00im-dlb-asmtpoutvip.me.com [10.52.196.131]) by ms11p00im-qufo17281701.me.com (Postfix) with ESMTPA id 3048A7428AA; Fri, 3 Mar 2023 14:44:06 +0000 (UTC) To: David Rowley Cc: pgsql-general@postgresql.org From: Conner Bean Subject: Re: Dropping behavior for unique CONSTRAINTs Date: Fri, 03 Mar 2023 14:44:05 -0000 X-Mailer: iCloud MailClientcurrent MailServer2308B104.10000-Famine2308-0-6c2ccbee4fd8 Message-id: Content-Type: multipart/alternative; boundary=Apple-Webmail-42--d76da638-b54b-46fe-9239-858753975bbf MIME-Version: 1.0 In-Reply-To: References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> X-Proofpoint-ORIG-GUID: nuHmAY3nJXU5KxzJAzozjmHxJ_xZDWmq X-Proofpoint-GUID: nuHmAY3nJXU5KxzJAzozjmHxJ_xZDWmq X-Proofpoint-Virus-Version: =?UTF-8?Q?vendor=3Dfsecure_engine=3D1.1.170-22c6f66c430a71ce266a39bfe25bc?= =?UTF-8?Q?2903e8d5c8f:6.0.138,18.0.572,17.11.62.513.0000000_definitions?= =?UTF-8?Q?=3D2020-02-14=5F11:2020-02-14=5F02,2020-02-14=5F11,2021-12-02?= =?UTF-8?Q?=5F01_signatures=3D0?= X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 adultscore=0 clxscore=1011 bulkscore=0 mlxlogscore=999 malwarescore=0 spamscore=0 mlxscore=0 suspectscore=0 phishscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2209130000 definitions=main-2303030127 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Webmail-42--d76da638-b54b-46fe-9239-858753975bbf Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8; format=flowed Ah, my apologies for missing that in the docs. I had previously noticed th= e CONCURRENTLY option on drop index, but I misread and incorrectly thought= that unique indexes themselves could not be dropped concurrently, rather = than that being true for only unique indexes=C2=A0backing constraints. Apo= logies on my misunderstanding!Thanks greatly for your help!Best,CSBOn Mar = 3, 2023, at 5:54 AM, David Rowley wrote:On Fri, 3 M= ar 2023 at 23:17, Conner Bean wrote:I wanted to a= void using a unique index since dropping them requires anexclusive lock an= d cannot be done concurrently. My thought was to thenuse a unique constrai= nt, since I've read unofficial docs[0] that saythese can be dropped safely= with no lock.You should try the official documents. You won't find any w= ording inthose that say that a unique constraint can be dropped without an= ylocking.If you look at https://www.postgresql.org/docs/current/sql-altert= able.htmlyou'll see "Note that the lock level required may differ for each= subform. An ACCESS EXCLUSIVE lock is acquired unless explicitlynoted.", an= d if you look at DROP CONSTRAINT that it mentions nothingabout any lower-l= evel locks, so you can assume that DROP CONSTRAINTobtains an access exclus= ive lock on the table being altered.If you have a look athttps://www.postg= resql.org/docs/15/sql-dropindex.html check out theCONCURRENTLY option. Tha= t option allows an index to be dropped withoutblocking concurrent reads an= d writes to the table. It seems like justhaving a unique index without the= constraint is likely your best betif you can't afford to block any traffi= c for the brief moment it wouldtake to drop the constraint.David --Apple-Webmail-42--d76da638-b54b-46fe-9239-858753975bbf Content-Type: multipart/related; type="text/html"; boundary=Apple-Webmail-86--d76da638-b54b-46fe-9239-858753975bbf --Apple-Webmail-86--d76da638-b54b-46fe-9239-858753975bbf Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8;
Ah, my apologies for missing that in the docs. I had previously = noticed the CONCURRENTLY option on drop index, but I misread and incorrect= ly thought that unique indexes themselves could not be dropped concurrentl= y, rather than that being true for only unique indexes backing con= straints. Apologies on my misunderstanding!

T= hanks greatly for your help!

Best,
<= div>CSB

On Mar 3, 2023,= at 5:54 AM, David Rowley <dgrowleyml@gmail.com> wrote:


On Fri, 3 Mar 2023 at 23:17, Con= ner Bean <conner.bean@icloud.com> wrote:
I wanted to a= void using a unique index since dropping them requires an
ex= clusive lock and cannot be done concurrently. My thought was to then
use a unique constraint, since I've read unofficial docs[0] that = say
these can be dropped safely with no lock.
=
You should try the official documents. You won't find any = wording in
those that say that a unique constraint can be dr= opped without any
locking.

you'll see "Note that the lock le= vel required may differ for each
subform. An ACCESS EXCLUSIV= E lock is acquired unless explicitly
noted.", and if you loo= k at DROP CONSTRAINT that it mentions nothing
about any lowe= r-level locks, so you can assume that DROP CONSTRAINT
obtain= s an access exclusive lock on the table being altered.

<= /div>
If you have a look at
<= div>CONCURRENTLY option. That option allows an index to be dropped without=
blocking concurrent reads and writes to the table. It seems= like just
having a unique index without the constraint is l= ikely your best bet
if you can't afford to block any traffic= for the brief moment it would
take to drop the constraint.<= br>

David
=

--Apple-Webmail-86--d76da638-b54b-46fe-9239-858753975bbf-- --Apple-Webmail-42--d76da638-b54b-46fe-9239-858753975bbf--