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 1pY2Tu-0002b5-03 for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 10:17:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pY2Ts-0002c4-Ri for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 10:17:40 +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 1pXpZg-0002O5-7d for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 20:30:48 +0000 Received: from ms11p00im-qufo17281601.me.com ([17.58.38.53]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXpZc-0005Bi-Sf for pgsql-general@postgresql.org; Thu, 02 Mar 2023 20:30:47 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1677789042; bh=qvko433k8Km7Ygha/Q+Qniri21WzTWxpL80sjEhXZhw=; h=To:From:Subject:Date:Message-id:Content-Type:MIME-Version; b=B9bC16vUan0KnJfThvIiwZbQPzAsXLDvlF2qFc+Kn9QEpMpfO5ZDPmTgkpxE10app dLMp6mwg6Bz+LNu9T296dhswFi1b2NSTt0uWoH78uwCQpwd9wWaN0GbRWj0Al0y7SS fwU/44Tm5qUlGdFu3NfKvKvfz/9FPVjxTA9OCvtA1B9dDvobrVxHb72sOOLrlpTJoN KizvC8d73daFMjd2PMreZDet1d5RC8YeepyZfZx6QX5xDY7a5virjeleapyUnP/zer 957XdiYLaEmRdcAn04kbX48r1kq8+zhDukTrCf//2V4pC2NAZIw2UM91KQiSIOjb8P 3eqN4Km9NzG0g== Received: from ms11p00im-qufo17281601.me.com (ms11p00im-qufo17281601.me.com [17.58.38.53]) by ms11p00im-qufo17281601.me.com (Postfix) with ESMTPS id 139B6AA033B for ; Thu, 2 Mar 2023 20:30:42 +0000 (UTC) Received: from localhost (ms11p00im-dlb-asmtpoutvip.me.com [10.52.196.131]) by ms11p00im-qufo17281601.me.com (Postfix) with ESMTPA id EDBE2AA006B for ; Thu, 2 Mar 2023 20:30:41 +0000 (UTC) To: pgsql-general@postgresql.org From: Conner Bean Subject: Dropping behavior for unique CONSTRAINTs Date: Thu, 02 Mar 2023 20:30:41 -0000 X-Mailer: iCloud MailClientcurrent MailServer2308B104.10000-Famine2308-0-6c2ccbee4fd8 Message-id: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> Content-Type: multipart/alternative; boundary=Apple-Webmail-42--ad87a83d-6a46-4eee-906d-7e07888cd91e MIME-Version: 1.0 X-Proofpoint-GUID: fkg7JxTMfUKC9PAs_-4aIsSYDyr37z4e X-Proofpoint-ORIG-GUID: fkg7JxTMfUKC9PAs_-4aIsSYDyr37z4e 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.0.605.474.0000000_definitions?= =?UTF-8?Q?=3D2020-02-14=5F11:2020-02-14=5F02,2020-02-14=5F11,2020-01-23?= =?UTF-8?Q?=5F02_signatures=3D0?= X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 phishscore=0 suspectscore=0 malwarescore=0 spamscore=0 bulkscore=0 clxscore=1011 mlxscore=0 adultscore=0 mlxlogscore=639 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2209130000 definitions=main-2303020177 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Webmail-42--ad87a83d-6a46-4eee-906d-7e07888cd91e Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8; format=flowed Hi folks,I'm curious if there are any docs supporting the functionality be= hind dropping unique constraints. For context, I am interested in enforcin= g uniqueness on a column. This table is heavily used, and I plan on droppi= ng the constraint in the future. I wanted to avoid using a unique index si= nce dropping them requires an exclusive lock and cannot be done concurrent= ly. My thought was to then use a unique constraint, since I've read unoffi= cial docs[0] that say these can be dropped safely with no lock.However, si= nce a unique index would be the backing index to the unique constraint, I'= m curious how this would work in practice (or if it even does!). Specifica= lly if my theory of: "I can use a unique constraint to avoid obtaining an = exclusive lock on my table" actually works.Thanks for any and all informat= ion, it's greatly appreciated, and apologies if I missed any existing docu= mentation.Best,=C2=A0CSB0:=C2=A0https://leopard.in.ua/2016/09/20/safe-and-= unsafe-operations-postgresql#.ZAEFpezMJLR=C2=A0 --Apple-Webmail-42--ad87a83d-6a46-4eee-906d-7e07888cd91e Content-Type: multipart/related; type="text/html"; boundary=Apple-Webmail-86--ad87a83d-6a46-4eee-906d-7e07888cd91e --Apple-Webmail-86--ad87a83d-6a46-4eee-906d-7e07888cd91e Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8;
Hi folks,

I'm curious if there are any d= ocs supporting the functionality behind dropping unique constraints. For c= ontext, I am interested in enforcing uniqueness on a column. This table is= heavily used, and I plan on dropping the constraint in the future. I want= ed to avoid using a unique index since dropping them requires an exclusive= 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.

However, sinc= e a unique index would be the backing index to the unique constraint, I'm = curious how this would work in practice (or if it even does!). Specificall= y if my theory of: "I can use a unique constraint to avoid obtaining an= exclusive lock on my table" actually works.

<= div>Thanks for any and all information, it's greatly appreciated, and apol= ogies if I missed any existing documentation.

Best, 
CSB

=

--Apple-Webmail-86--ad87a83d-6a46-4eee-906d-7e07888cd91e-- --Apple-Webmail-42--ad87a83d-6a46-4eee-906d-7e07888cd91e--