Output some hints for common migration issues

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:14:56.563273317+00:00	1004	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1001
public	books	AccessShareLock	Table	16415	✅	1001
public	books	ShareRowExclusiveLock	Table	16415	❌	1001
public	books_pkey	AccessShareLock	Index	16421	✅	1001

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. It is safer to add constraints as NOT VALID and validate them in later transactions, to avoid holding dangerous locks for a long time. a NOT VALID constraint will validate all new inserts and updates, but not existing data.

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	16415	❌

New locks taken

No new locks taken by this statement.

Statement number 4 for 0 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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessShareLock	Table	16415	✅
public	books	ShareRowExclusiveLock	Table	16415	❌
public	books_pkey	AccessShareLock	Index	16421	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating in a later transaction minimizes time spent holding dangerous locks.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:16:16.079950952+00:00	1005	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002
public	books	AccessShareLock	Table	16415	✅	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. It is safer to add constraints as NOT VALID and validate them in later transactions, to avoid holding dangerous locks for a long time. a NOT VALID constraint will validate all new inserts and updates, but not existing data.

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	16415	❌

New locks taken

No new locks taken by this statement.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books_pkey	AccessShareLock	Index	16421	✅
public	books	AccessShareLock	Table	16415	✅
public	books	ShareRowExclusiveLock	Table	16415	❌

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating in a later transaction minimizes time spent holding dangerous locks.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:22:25.673838488+00:00	1005	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books	AccessShareLock	Table	16415	✅	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:

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

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	16415	❌

New locks taken

No new locks taken by this statement.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessShareLock	Table	16415	✅
public	books	ShareRowExclusiveLock	Table	16415	❌
public	books_pkey	AccessShareLock	Index	16421	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating in a later transaction minimizes time spent holding dangerous locks.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:26:08.873830228+00:00	1004	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books	AccessShareLock	Table	16415	✅	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002

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 2 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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:

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

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	16415	❌

New locks taken

No new locks taken by this statement.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	AccessShareLock	Table	16415	✅
public	books	ShareRowExclusiveLock	Table	16415	❌
public	books_pkey	AccessShareLock	Index	16421	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating with ALTER TABLE ... VALIDATE CONSTRAINT ... in a later transaction minimizes time spent holding dangerous locks.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:30:32.901945792+00:00	1006	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002
public	books	AccessShareLock	Table	16415	✅	1002

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:

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

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	16415	❌

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books. This lock blocks all other transactions from accessing the database object. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	ShareRowExclusiveLock	Table	16415	❌
public	books_pkey	AccessShareLock	Index	16421	✅
public	books	AccessShareLock	Table	16415	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating with ALTER TABLE ... VALIDATE CONSTRAINT ... in a later transaction minimizes time spent holding dangerous locks.

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books. This lock blocks all other transactions from accessing the database object. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books. This lock blocks all other transactions from accessing the database object. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books. This lock blocks all other transactions from accessing the database object. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:42:16.002982811+00:00	1005	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002
public	books	AccessShareLock	Table	16415	✅	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:

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

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	16415	❌

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books_pkey	AccessShareLock	Index	16421	✅
public	books	AccessShareLock	Table	16415	✅
public	books	ShareRowExclusiveLock	Table	16415	❌

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating with ALTER TABLE ... VALIDATE CONSTRAINT ... in a later transaction minimizes time spent holding dangerous locks.

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table books, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

[kaaveland]

Eugene 🔒 trace of add_authors.sql

In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.

We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.

For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.

Overall Summary

Started at	Total duration (ms)	Number of dangerous locks
2024-05-05T12:48:09.234165630+00:00	1005	2 ❌

All locks acquired

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
public	books	AccessExclusiveLock	Table	16415	❌	1002
public	books	ShareRowExclusiveLock	Table	16415	❌	1002
public	books	AccessShareLock	Table	16415	✅	1002
public	books_pkey	AccessShareLock	Index	16421	✅	1002

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	16415	❌

Hints

Validating table with a new NOT NULL column

ID: make_column_not_nullable_with_lock

The column title in the table public.books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:

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

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	16415	❌

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

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	16415	❌

New locks taken

Schema	Object	Mode	Relkind	OID	Safe
public	books	ShareRowExclusiveLock	Table	16415	❌
public	books	AccessShareLock	Table	16415	✅
public	books_pkey	AccessShareLock	Index	16421	✅

Hints

Validating table with a new constraint

ID: validate_constraint_with_lock

A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating with ALTER TABLE ... VALIDATE CONSTRAINT ... in a later transaction minimizes time spent holding dangerous locks.

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 5 for 1001 ms

SQL

select pg_sleep(1);

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.

Statement number 6 for 0 ms

SQL

select * from books;

Locks at start

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

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: holding_access_exclusive

The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.