Add hint about lock_timeout

[kaaveland]

Solves #19

[kaaveland]

Eugene 🔒 trace report of add_authors.sql

This is a human readable lock tracing and migration report generated by eugene to assist you in writing safer database migration scripts.

Here are some tips for reading it:

• A lock is called dangerous ❌ if it will cause concurrent queries to wait for the migration to complete
• You read that right, once a lock is acquired, it is only released at the end of the script
• Eugene will tell you what kinds of queries dangerous locks would block in a summary
• Hints can sometimes help you avoid dangerous locks, or hold them for a shorter time
• It is hard to avoid dangerous locks, but we should minimize time spent while holding them
• Sometimes seemingly fast migration scripts cause long outages because of lock queues, here is an example scenario

There is a summary section for the entire script at the start of the report and then a section for each statement in the script, that goes over the state of the database at the time the script was executed, as well as effects or hints specific to that particular statement

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-06T18:35:53.405235506+00:00	4	2 ❌

All locks found

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16416	❌	1
public	books	AccessShareLock	Table	16416	✅	1
public	books	ShareRowExclusiveLock	Table	16416	❌	1
public	books_pkey	AccessShareLock	Index	16422	✅	1

Dangerous locks found

• AccessExclusiveLock would block the following operations on public.books:
  • SELECT
  • FOR UPDATE
  • FOR NO KEY UPDATE
  • FOR SHARE
  • FOR KEY SHARE
  • UPDATE
  • DELETE
  • INSERT
  • MERGE
• ShareRowExclusiveLock would block the following operations on public.books:
  • UPDATE
  • DELETE
  • INSERT
  • MERGE

Statement number 1 for 3 ms

SQL

create table authors(id serial primary key, name text not null);

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 0 ms

SQL

alter table books alter column title set not null;

Locks at start

No locks held at the start of this statement.

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

A column was changed from NULL to NOT NULL. This blocks all table access until all rows are validated. A safer way is: Add a CHECK constraint as NOT VALID, validate it later, then make the column NOT NULL.

The column title in the table public.books was changed to NOT NULL. If there is a CHECK (title IS NOT NULL) constraint on public.books, this is safe. Splitting this kind of change into 3 steps can make it safe:

1. Add a CHECK (title IS NOT NULL) NOT VALID; constraint on public.books.
2. Validate the constraint in a later transaction, with ALTER TABLE public.books VALIDATE CONSTRAINT ....
3. Make the column NOT NULL

Taking dangerous lock without timeout

ID: dangerous_lock_without_timeout

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 lock_timeout = '2s'; before the statement and retry the migration if necessary.

The statement took AccessExclusiveLock on the Table public.books 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.

Statement number 3 for 0 ms

SQL

alter table books add column author_id integer not null;

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.

Statement number 4 for 1 ms

SQL

alter table books add foreign key (author_id) references authors(id);

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessShareLock	Table	16416	✅
public	books	ShareRowExclusiveLock	Table	16416	❌
public	books_pkey	AccessShareLock	Index	16422	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

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 books_author_id_fkey of type FOREIGN KEY was added to the table books as VALID. Constraints that are NOT VALID can be made VALID by ALTER TABLE public.books VALIDATE CONSTRAINT books_author_id_fkey which takes a lesser lock.

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.

Taking dangerous lock without timeout

ID: dangerous_lock_without_timeout

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 lock_timeout = '2s'; before the statement and retry the migration if necessary.

The statement took ShareRowExclusiveLock on the Table public.books without a timeout. It blocks UPDATE, DELETE, INSERT, MERGE while waiting to acquire the lock.

Statement number 5 for 0 ms

SQL

select * from books;

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌
public	books	AccessShareLock	Table	16416	✅
public	books	ShareRowExclusiveLock	Table	16416	❌
public	books_pkey	AccessShareLock	Index	16422	✅

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.

[kaaveland]

Eugene 🔒 trace report of add_authors.sql

This is a human readable lock tracing and migration report generated by eugene to assist you in writing safer database migration scripts.

Here are some tips for reading it:

• A lock is called dangerous ❌ if it will cause concurrent queries to wait for the migration to complete
• You read that right, once a lock is acquired, it is only released at the end of the script
• Eugene will tell you what kinds of queries dangerous locks would block in a summary
• Hints can sometimes help you avoid dangerous locks, or hold them for a shorter time
• It is hard to avoid dangerous locks, but we should minimize time spent while holding them
• Sometimes seemingly fast migration scripts cause long outages because of lock queues, here is an example scenario

There is a summary section for the entire script at the start of the report and then a section for each statement in the script, that goes over the state of the database at the time the script was executed, as well as effects or hints specific to that particular statement

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-06T18:39:31.713433668+00:00	5	2 ❌

All locks found

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16416	❌	1
public	books	AccessShareLock	Table	16416	✅	1
public	books	ShareRowExclusiveLock	Table	16416	❌	1
public	books_pkey	AccessShareLock	Index	16422	✅	1

Dangerous locks found

• AccessExclusiveLock would block the following operations on public.books:
  • SELECT
  • FOR UPDATE
  • FOR NO KEY UPDATE
  • FOR SHARE
  • FOR KEY SHARE
  • UPDATE
  • DELETE
  • INSERT
  • MERGE
• ShareRowExclusiveLock would block the following operations on public.books:
  • UPDATE
  • DELETE
  • INSERT
  • MERGE

Statement number 1 for 4 ms

SQL

create table authors(id serial primary key, name text not null);

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 0 ms

SQL

alter table books alter column title set not null;

Locks at start

No locks held at the start of this statement.

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

A column was changed from NULL to NOT NULL. This blocks all table access until all rows are validated. A safer way is: Add a CHECK constraint as NOT VALID, validate it later, then make the column NOT NULL.

The column title in the table public.books was changed to NOT NULL. If there is a CHECK (title IS NOT NULL) constraint on public.books, this is safe. Splitting this kind of change into 3 steps can make it safe:

1. Add a CHECK (title IS NOT NULL) NOT VALID; constraint on public.books.
2. Validate the constraint in a later transaction, with ALTER TABLE public.books VALIDATE CONSTRAINT ....
3. Make the column NOT NULL

Taking dangerous lock without timeout

ID: dangerous_lock_without_timeout

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 lock_timeout = '2s'; before the statement and retry the migration if necessary.

The statement took AccessExclusiveLock on the Table public.books 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.

Statement number 3 for 0 ms

SQL

alter table books add column author_id integer not null;

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.

Statement number 4 for 1 ms

SQL

alter table books add foreign key (author_id) references authors(id);

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessShareLock	Table	16416	✅
public	books	ShareRowExclusiveLock	Table	16416	❌
public	books_pkey	AccessShareLock	Index	16422	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

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 books_author_id_fkey of type FOREIGN KEY was added to the table books as VALID. Constraints that are NOT VALID can be made VALID by ALTER TABLE public.books VALIDATE CONSTRAINT books_author_id_fkey which takes a lesser lock.

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.

Taking dangerous lock without timeout

ID: dangerous_lock_without_timeout

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 lock_timeout = '2s'; before the statement and retry the migration if necessary.

The statement took ShareRowExclusiveLock on the Table public.books without a timeout. It blocks UPDATE, DELETE, INSERT, MERGE while waiting to acquire the lock.

Statement number 5 for 0 ms

SQL

select * from books;

Locks at start

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessExclusiveLock	Table	16416	❌
public	books	AccessShareLock	Table	16416	✅
public	books	ShareRowExclusiveLock	Table	16416	❌
public	books_pkey	AccessShareLock	Index	16422	✅

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

A transaction that holds an AccessExclusiveLock started a new statement. This blocks all access to the table for the duration of this statement. A safer way is: Run this statement in a new transaction.

The statement is running while holding an AccessExclusiveLock on the Table public.books, blocking all other transactions from accessing it.