pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Conner Bean <conner.bean@icloud.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Dropping behavior for unique CONSTRAINTs
Date: Fri, 03 Mar 2023 14:44:05 -0000
Message-ID: <d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com> (raw)
In-Reply-To: <CAApHDvpTsAUkiakGDzJGxMVG0H1vq=h51+107B6ERe5XfD5T-A@mail.gmail.com>
References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com>
	<CAApHDvpTsAUkiakGDzJGxMVG0H1vq=h51+107B6ERe5XfD5T-A@mail.gmail.com>

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 <dgrowleyml@gmail.com> 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 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

view thread (10+ messages)  latest in thread

Message-ID: <d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com>
Permalink:  ../d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com/
Also on:    postgresql.org/message-id/d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: conner.bean@icloud.com, dgrowleyml@gmail.com
  Subject: Re: Dropping behavior for unique CONSTRAINTs
  In-Reply-To: <d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com>
  References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> <CAApHDvpTsAUkiakGDzJGxMVG0H1vq=h51+107B6ERe5XfD5T-A@mail.gmail.com> <d9ab5280-2aff-4265-8f7b-0cdcca836d17@me.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox