Thread: Dropping behavior for unique CONSTRAINTs

Dropping behavior for unique CONSTRAINTs

From
Conner Bean
Date:
Hi folks,

I'm curious if there are any docs supporting the functionality behind dropping unique constraints. For context, I am interested in enforcing uniqueness on a column. This table is heavily used, and I plan on dropping the constraint in the future. 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.

However, since a unique index would be the backing index to the unique constraint, I'm curious how this would work in practice (or if it even does!). Specifically if my theory of: "I can use a unique constraint to avoid obtaining an exclusive lock on my table" actually works.

Thanks for any and all information, it's greatly appreciated, and apologies if I missed any existing documentation.

Best, 
CSB



Re: Dropping behavior for unique CONSTRAINTs

From
David Rowley
Date:
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



Re: Dropping behavior for unique CONSTRAINTs

From
Conner Bean
Date:
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@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 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.

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


Re: Dropping behavior for unique CONSTRAINTs

From
Ron
Date:
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.

Re: Dropping behavior for unique CONSTRAINTs

From
David Rowley
Date:
On Sat, 4 Mar 2023 at 10:55, Ron <ronljohnsonjr@gmail.com> wrote:
> On 3/3/23 04:54, David Rowley wrote:
> 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
CASCADEoption is not supported. (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this
way.)
> "

I'm not sure which one of these you think applies to the
recommendation I mentioned or if you were just generally highlighting
the limitations of DROP INDEX CONCURRENTLY.

David



Re: Dropping behavior for unique CONSTRAINTs

From
"Peter J. Holzer"
Date:
On 2023-03-02 20:30:41 -0000, Conner Bean wrote:
> Hi folks,I'm curious if there are any docs supporting the
> functionality behind dropping unique constraints. For context, I am
> interested in enforcing uniqueness on a column. This table is heavily
> used, and I plan on dropping the constraint in the future. 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.However, since a unique index would
> be the backing index to the unique constraint, I'm curious how this
> would work in practice (or if it even does!).

So your plan is to create a unique constraint (backed by a unique
index) and then to drop the index and keep the constraint?

That doesn't work. A unique constraint can't exist without a (unique)
index. Think about it: With a unique constraint PostgreSQL needs to
check for every insert whether the value already exists in the table.
Without an index this would mean a full table scan.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

Re: Dropping behavior for unique CONSTRAINTs

From
"Peter J. Holzer"
Date:
On 2023-03-04 13:50:28 +1300, David Rowley wrote:
> On Sat, 4 Mar 2023 at 10:55, Ron <ronljohnsonjr@gmail.com> wrote:
> > On 3/3/23 04:54, David Rowley wrote:
> > 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.)
> > "
>
> I'm not sure which one of these you think applies to the
> recommendation I mentioned

The OP asked specifically about dropping the index backing a unique
constraint, so Ron is pointing out that CONCURRENTLY cannot be used for
that purpose.

(I realize that your idea is not to create the constraint in the first
place.)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

Re: Dropping behavior for unique CONSTRAINTs

From
Ron
Date:
On 3/4/23 02:03, Peter J. Holzer wrote:
[snip]
> So your plan is to create a unique constraint (backed by a unique
> index) and then to drop the index and keep the constraint?
>
> That doesn't work. A unique constraint can't exist without a (unique)
> index. Think about it: With a unique constraint PostgreSQL needs to
> check for every insert whether the value already exists in the table.
> Without an index this would mean a full table scan.

I cut my teeth on an RDBMS which didn't automagically create a backing 
index.  You had to do it yourself...

(Autocommit and the default transaction mode not being SERIALIZABLE were 
also a shock when I started using other systems.)

-- 
Born in Arizona, moved to Babylonia.



Re: Dropping behavior for unique CONSTRAINTs

From
"Peter J. Holzer"
Date:
On 2023-03-04 02:34:02 -0600, Ron wrote:
> On 3/4/23 02:03, Peter J. Holzer wrote:
> [snip]
> > So your plan is to create a unique constraint (backed by a unique
> > index) and then to drop the index and keep the constraint?
> >
> > That doesn't work. A unique constraint can't exist without a (unique)
> > index. Think about it: With a unique constraint PostgreSQL needs to
> > check for every insert whether the value already exists in the table.
> > Without an index this would mean a full table scan.
>
> I cut my teeth on an RDBMS which didn't automagically create a backing
> index.  You had to do it yourself...

Just curious: Which RDBMS was that?

I'm pretty sure Oracle did that automatically when I first used it
(version 8.0.5). Not sure about MySQL, but if it didn't have an index it
probably didn't enfocre the constraint either (it definitely didn't
enforce foreign key constraints).

Speaking of foreign key constraints: Neither Oracle nor PostgreSQL
automatically add an index on a foreign key. That bit me hard back in
the day ...

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

Re: Dropping behavior for unique CONSTRAINTs

From
Ron
Date:
On 3/4/23 05:51, Peter J. Holzer wrote:
> On 2023-03-04 02:34:02 -0600, Ron wrote:
>> On 3/4/23 02:03, Peter J. Holzer wrote:
>> [snip]
>>> So your plan is to create a unique constraint (backed by a unique
>>> index) and then to drop the index and keep the constraint?
>>>
>>> That doesn't work. A unique constraint can't exist without a (unique)
>>> index. Think about it: With a unique constraint PostgreSQL needs to
>>> check for every insert whether the value already exists in the table.
>>> Without an index this would mean a full table scan.
>> I cut my teeth on an RDBMS which didn't automagically create a backing
>> index.  You had to do it yourself...
> Just curious: Which RDBMS was that?

Rdb/VMS (the DEC product for OpenVMS, which has been owned by Oracle for the 
past 25 years).

> Speaking of foreign key constraints: Neither Oracle nor PostgreSQL
> automatically add an index on a foreign key. That bit me hard back in
> the day ...

Us too.  (Well, the developer, from before I arrived.)

-- 
Born in Arizona, moved to Babylonia.