Ah, my apologies for missing that in the docs. I had previously noticed the 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 backing constraints. Apologies on
my misunderstanding!Thanks greatly for your help!Best,CSBOn Mar 3, 2023, at 5:54 AM, David Rowley
<dgrowle...@gmail.com> wrote:On Fri, 3 Mar 2023 at 23:17, Conner Bean
<conner.b...@icloud.com> wrote:I wanted to avoid using a unique index since dropping them
requires anexclusive lock and cannot be done concurrently. My thought was to thenuse a unique
constraint, 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 wording inthose that say that a unique
constraint can be dropped without anylocking.If you look at
https://www.postgresql.org/docs/current/sql-altertable.htmlyou'll see "Note that the lock level
required may differ for eachsubform. An ACCESS EXCLUSIVE lock is acquired unless
explicitlynoted.", and if you look at DROP CONSTRAINT that it mentions nothingabout any
lower-level locks, so you can assume that DROP CONSTRAINTobtains an access exclusive lock on the
table being altered.If you have a look athttps://www.postgresql.org/docs/15/sql-dropindex.html check
out theCONCURRENTLY option. That option allows an index to be dropped withoutblocking concurrent
reads and 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 traffic for the brief moment it wouldtake to
drop the constraint.David
