Eugene 🔒 trace report of examples/W14/good/1.sql
Statement number 1 for 10ms
SQL
-- 1.sql
create table authors(
name text
)
Locks at start
No locks held at the start of this statement.
New locks taken
No new locks taken by this statement.
Eugene 🔒 trace report of examples/W14/good/2.sql
Statement number 1 for 10ms
SQL
-- 2.sql
create unique index concurrently
authors_name_key on authors(name)
Locks at start
No locks held at the start of this statement.
New locks taken
No new locks taken by this statement.
Eugene 🔒 trace report of examples/W14/good/3.sql
Statement number 1 for 10ms
SQL
-- 3.sql
set local lock_timeout = '2s'
Locks at start
No locks held at the start of this statement.
New locks taken
No new locks taken by this statement.
Statement number 2 for 10ms
SQL
-- eugene: ignore E2
-- This is a demo of W14, so we can ignore E2 instead of the
-- multi-step migration to make the column NOT NULL safely
alter table authors
alter column name set not null
Locks at start
No locks held at the start of this statement.
New locks taken
Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
---|---|---|---|---|---|---|
public | authors | AccessExclusiveLock | Table | 1 | ❌ | 10 |
Eugene 🔒 trace report of examples/W14/good/4.sql
Statement number 1 for 10ms
SQL
-- 4.sql
alter table authors
add constraint authors_name_pkey
primary key using index authors_name_key
Locks at start
No locks held at the start of this statement.
New locks taken
Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
---|---|---|---|---|---|---|
public | authors | AccessExclusiveLock | Table | 1 | ❌ | 10 |
Hints
Validating table with a new constraint
ID: E1
A new constraint was added and it is already VALID
. This blocks all table access until all rows are validated. A safer way is: Add the constraint as NOT VALID
and validate it with ALTER TABLE ... VALIDATE CONSTRAINT
later.
A new constraint authors_name_pkey
of type PRIMARY KEY
was added to the table public.authors
as VALID
. Constraints that are NOT VALID
can be made VALID
by ALTER TABLE public.authors VALIDATE CONSTRAINT authors_name_pkey
which takes a lesser lock.
Taking dangerous lock without timeout
ID: E9
A lock that would block many common operations was taken without a timeout. This can block all other operations on the table indefinitely if any other transaction holds a conflicting lock while idle in transaction
or active
. A safer way is: Run SET LOCAL lock_timeout = '2s';
before the statement and retry the migration if necessary.
The statement took AccessExclusiveLock
on the Table public.authors
without a timeout. It blocks SELECT
, FOR UPDATE
, FOR NO KEY UPDATE
, FOR SHARE
, FOR KEY SHARE
, UPDATE
, DELETE
, INSERT
, MERGE
while waiting to acquire the lock.