Question 216100 in PostgreSQL Question posted in
The official documentation can be found here.

Concurrently drop an unique constraint in PostgresQL?

We have this large table in our Postgres DB, let’s call it DataTable with a decent amount of read/write operations.

There currently exists a unique constraint on this table that I would like to get rid of and instead replace it with a different unique constraint. I see that we can reduce the downtime of the table for index creation by using CONCURRENTLY for index creation/deletion but is there a similar way to simulate DROP CONSTRAINT CONCURRENTLY for a unique index?

If not, how can I remove this constraint safely?

Tags:

2

Answers


  1. 0

    In PostgreSQL, you cannot use DROP CONSTRAINT CONCURRENTLY. You can achieve what you want in two steps:

    1. Create a new Constraint, and Alter the table to add that constraint.

    2. Drop the already implemented Unique constraint.

    If you don’t know the name of an already implemented UNIQUE constraint, you can get its auto-generated name through pg admin.

    Login or Signup to reply.
  2. 0

    There is no need for a CONCURRENTLY clause when dropping a constraint, because that operation is always very fast.

    If you experience something else, that is because you have long running transactions on that table. Such transactions will block the ACCESS EXCLUSIVE lock that ALTER TABLE needs on the table for a very short time. You will have to find a point in time when there is no long running transaction using the table.

    To avoid problems caused by waiting for a lock, use a technique like this:

    SET lock_timeout = '1s';
ALTER TABLE tab DROP CONSTRAINT myconstr;

    If the statement is blocked for more than a second, it will terminate wiht an error. Keep repeating that until it succeeds.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search