Thread: Dropping behavior for unique CONSTRAINTs
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
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
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 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
On 3/3/23 04:54, David Rowley wrote:
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.)
"
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.
Born in Arizona, moved to Babylonia.
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
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
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
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.
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
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.