Support concurrently with commit

[kaaveland]

With --commit, we can run several concurrently-statements with auto-commit. Without, we still let it fail.

Add snapshot test for create index concurrently and testcase for tracer behaviour.

[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-06T07:47:04.834292838+00:00	2	2 ❌

All locks found

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

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

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. Splitting this kind of change into 3 steps can make it safer:

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

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 public.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 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	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 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 public.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 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

The statement is running while holding an AccessExclusiveLock on the Table public.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 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-06T08:49:46.369337878+00:00	3	2 ❌

All locks found

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

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

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. Splitting this kind of change into 3 steps can make it safer:

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

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 public.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 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	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 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 public.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 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

The statement is running while holding an AccessExclusiveLock on the Table public.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.