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.
David
