E7 Creating a new unique constraint
Description
Triggered when: Adding a new unique constraint implicitly creates index.
Effect: This blocks all writes to the table while the index is being created and validated.
Workaround: CREATE UNIQUE INDEX CONCURRENTLY, then add the constraint using the index.
Detected by: eugene lint and eugene trace
Problematic migration
-- 1.sql
create table authors(
id integer generated always as identity
primary key,
name text not null
);
-- 2.sql
set local lock_timeout = '2s';
alter table authors
add constraint unique_name unique(name);
Safer migration
-- 1.sql
create table authors(
id integer generated always as identity
primary key,
name text not null
);
-- 2.sql
create unique index concurrently
authors_name_unique on authors(name);
-- 3.sql
set local lock_timeout = '2s';
alter table authors
add constraint unique_name
unique using index authors_name_unique;