Skip to main content

disallowed-unique-constraint

problem​

Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock which blocks reads and writes to the table while the index is built.

solution​

Instead create an index CONCURRENTLY and create the CONSTRAINT USING the index.

https://www.postgresql.org/docs/current/sql-altertable.html

Instead of:

-- blocks reads and writes to table_name while constraint is validated.
ALTER TABLE distributors ADD CONSTRAINT dist_id_uniq UNIQUE (dist_id);

Use:

-- allows reads and writes while index is built
CREATE UNIQUE INDEX CONCURRENTLY dist_id_uniq ON distributors (dist_id);
-- add constraint using the built index
ALTER TABLE distributors ADD CONSTRAINT distributors_dist_id_key UNIQUE USING INDEX dist_id_uniq;

Instead of:

-- blocks reads and writes to table_name while constraint is validated.
ALTER TABLE distributors
    ADD COLUMN dist_id text CONSTRAINT dist_id_uniq UNIQUE (dist_id);

Use:

-- add nullable column separately to prevent blocking read/writes.
ALTER TABLE distributors ADD COLUMN dist_id text;
-- allows reads and writes while index is built
CREATE UNIQUE INDEX CONCURRENTLY dist_id_uniq ON distributors (dist_id);
-- add constraint using the built index
ALTER TABLE distributors ADD CONSTRAINT distributors_dist_id_key UNIQUE USING INDEX dist_id_uniq;

solution for alembic and sqlalchemy​

# migrations/*.py
# First migration
from alembic import op

def schema_upgrades():
    with op.get_context().autocommit_block():
        op.create_index(
            op.f("dist_id_uniq"),
            "distributors",
            ["dist_id"],
            unique=True,
            postgresql_concurrently=True,
        )

def schema_downgrades():
    with op.get_context().autocommit_block():
        op.drop_index(
            op.f("dist_id_uniq"),
            postgresql_concurrently=True,
        )
# migrations/*.py
# Second migration
from alembic import op
import sqlalchemy as sa

def schema_upgrades():
    op.execute(
        sa.text(
            """
            ALTER TABLE distributors ADD CONSTRAINT distributors_dist_id_key UNIQUE USING INDEX dist_id_uniq;
            """
        ),
    )

def schema_downgrades():
    op.drop_constraint(
        "distributors_dist_id_key",
        type_="unique",
    )