Re: Dropping behavior for unique CONSTRAINTs

[Conner Bean <conner.bean@xxxxxxxxxx>]

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,

CSB

On Mar 3, 2023, at 5:54 AM, David Rowley <dgrowleyml@xxxxxxxxx> wrote:

On Fri, 3 Mar 2023 at 23:17, Conner Bean <conner.bean@xxxxxxxxxx> 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.

David