Re: Dropping behavior for unique CONSTRAINTs

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"