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 1pYDNE-0005rD-UK for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 21:55:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYDNC-000337-CD for pgsql-general@arkaria.postgresql.org; Fri, 03 Mar 2023 21:55:30 +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 1pYDNB-00032v-WA for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 21:55:30 +0000 Received: from mail-qt1-x82c.google.com ([2607:f8b0:4864:20::82c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYDN9-00085g-Bz for pgsql-general@lists.postgresql.org; Fri, 03 Mar 2023 21:55:29 +0000 Received: by mail-qt1-x82c.google.com with SMTP id c3so4514393qtc.8 for ; Fri, 03 Mar 2023 13:55:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677880525; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=2JIVhlwluzWE9udoFxv+WuJdQSmknfyjPAeoGOAJ4Ks=; b=gUq7n/Qv4y6KHm70LnMOtQxp3NIYVLUFoaTBXNCdC97X49c/nbnRgrrALCphwPeTkp w1kTqyJSzye25LO8/cuKFtEFf8awT3b1pG5apveCV7eEUyqg52OQ7CuIkG0/nECNlq6U o3U6nQ9jflZDpgOuFLtnF6Kpm9X4mz243zv0sw64vg33S/VzixhFf91WL6rxekOqde9v wsrY25mBYALUP++/Ry+Foimq//OylScsrXNfkOb48EfgceFs4M5i1tKW6TbwfFvwFavE jZQPWorBJc7A8qe8UZ6AWleLMUJMlMDjJKv5SMAb8wxJjMmRL2XaEQYYWl8HM2rxK4bu a9dg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677880525; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=2JIVhlwluzWE9udoFxv+WuJdQSmknfyjPAeoGOAJ4Ks=; b=ul1s2Y83bMTYajTtSUYA2ZwEIpmrSs2fI01MRlq09QlTNpmwIe+t5/N+CADgWTSnru DW7CPSwLemer0DCSM0TcqmIhem0tKAjUJZD7ui3F+2vlYEDKgu/gzLOZz1Aeq62hg+6a zGB9W7tgheduLP724Vmhfm1NotjvOAFujI00uWHvsQ+Q7EIENxFeCSjAfPNSVmTIQHwa VN3OdZ8qwBJdpsykPkuc5T1TKe87yTO8vO74LCF4PS+GIomlHhvLhaOu35Cd/tqCUXBw 1F/tJnP09dZOM3dywD6XP5vVnP2HJGDSKd1SivKLXCZyh/uR8cGOznzY1f2nQdggg1Na jKYA== X-Gm-Message-State: AO0yUKU/IAyQsYD8QNJFuVfNYyy1E3jPzt2dtHAe9bqB/lCFbo8NfvMm QcpN0T2VAD1uILBaDCeuoDrTh1JZnBc= X-Google-Smtp-Source: AK7set+xNja/LoZkvEewOSYWypvcCCp7Ao/348k8NmkoEszGMLAgfOYd/GHhvLPi20XmB60TI7hUgw== X-Received: by 2002:ac8:5f88:0:b0:3bf:daae:7f24 with SMTP id j8-20020ac85f88000000b003bfdaae7f24mr5577855qta.34.1677880525173; Fri, 03 Mar 2023 13:55:25 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id h13-20020ac8514d000000b003bfc335f124sm2440120qtn.79.2023.03.03.13.55.24 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 03 Mar 2023 13:55:24 -0800 (PST) Content-Type: multipart/alternative; boundary="------------b3mIrn0DNd990IlpMMsk7tef" Message-ID: <60c7fb5d-57ba-7c49-affc-e6d1739772a7@gmail.com> Date: Fri, 3 Mar 2023 15:55:24 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Dropping behavior for unique CONSTRAINTs Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------b3mIrn0DNd990IlpMMsk7tef Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 3/3/23 04:54, David Rowley wrote: > On Fri, 3 Mar 2023 at 23:17, Conner Bean wrote: > I wanted 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. > > You should try the official documents. You won't find any wording in > those that say that a unique constraint can be dropped without any > locking. > > If you look athttps://www.postgresql.org/docs/current/sql-altertable.html > you'll see "Note that the lock level required may differ for each > subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly > noted.", and if you look at DROP CONSTRAINT that it mentions nothing > about any lower-level locks, so you can assume that DROP CONSTRAINT > obtains an access exclusive lock on the table being altered. > > If you have a look at > https://www.postgresql.org/docs/15/sql-dropindex.html check out the > 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 likely your best bet > if you can't afford to block any traffic for the brief moment it would > take to drop the constraint. That doc page says this about CONCURRENTLY: " There are several caveats to be aware of when using this option. Only one index name can be specified, and the CASCADE option is not supported. (Thus, *an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way.*) " -- Born in Arizona, moved to Babylonia. --------------b3mIrn0DNd990IlpMMsk7tef Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit On 3/3/23 04:54, David Rowley wrote:
On Fri, 3 Mar 2023 at 23:17, Conner Bean <conner.bean@icloud.com> wrote:
I wanted 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.

You should try the official documents.  You won't find any wording in
those that say that a unique constraint can be dropped without any
locking.

If you look at https://www.postgresql.org/docs/current/sql-altertable.html
you'll see "Note that the lock level required may differ for each
subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly
noted.", and if you look at DROP CONSTRAINT that it mentions nothing
about any lower-level locks, so you can assume that DROP CONSTRAINT
obtains an access exclusive lock on the table being altered.

If you have a look at
https://www.postgresql.org/docs/15/sql-dropindex.html check out the
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 likely your best bet
if you can't afford to block any traffic for the brief moment it would
take to drop the constraint.

That doc page says this about CONCURRENTLY:
"
There are several caveats to be aware of when using this option. Only one index name can be specified, and the CASCADE option is not supported. (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way.)
"

--
Born in Arizona, moved to Babylonia.
--------------b3mIrn0DNd990IlpMMsk7tef--