Introduction to eugene

Eugene helps you write zero downtime schema migration scripts for PostgreSQL databases. Sometimes, the most straightforward way to make a change to your database schema is also quite risky, due to locking issues and lock queues. Eugene has two modes that can help you spot dangerous patterns and can suggest a safer way to achieve the same effect in many cases, and it is easy to get started with both:

  • eugene lint will perform syntax tree analysis of your SQL script using the PostgreSQL parser.
  • eugene trace run your scripts in a temporary PostgreSQL server, and inspect locks.
  • Both understand git and can easily check only what's new on your branch, commit or tag.
  • Both handle folders with version named scripts, and run scripts in the right order.
  • Easy to run in CI, to post markdown reports to your PRs.

Feel free to try out Eugene by playing around with the SQL script in the text area below. When you click the "Check" button, Eugene will analyze the scripts and let you know if it found any issues.

-- You can use file markers like this to break migrations -- into steps and run them in order. -- file: create_table.sql create table books ( id serial primary key, title text, author text, published date ); -- file: alter_table.sql alter table books alter column title set not null; alter table books alter column author set not null; -- file: set_unique.sql set local lock_timeout = '2s'; alter table books add constraint unique_title_author unique (title, author);

The demo corresponds to using eugene lint on a folder of SQL scripts on your local machine. You can also use eugene trace to run the scripts, which can pick up more issues, some of which eugene lint can't detect.

Installing eugene

You can install eugene using cargo, but this requires you to have rust and some other build tools installed. To install rust, you can use rustup.

In addition to rust, you need:

  • gcc and g++ or clang and clang++
    • on macos, you get these with xcode-select --install
    • on ubuntu, install with sudo apt install clang
  • cmake
    • on macos, you can get this with brew install cmake
    • on ubuntu, you can get this with sudo apt install cmake

After you have rust and the other build tools installed, you can install eugene with:

cargo install eugene

It is also available as a Docker image:

docker run --rm -v $(pwd):/workdir \
  ghcr.io/kaaveland/eugene:latest \
  lint /workdir

Eugene is available as a binary for Linux and macOS. You can download the latest release from the releases page. Note that the binaries are not notarized and signed for macOS, so you may need to allow the binary to run by removing its quarantine attribute:

xattr -d com.apple.quarantine eugene

Source code and issue tracker

The source code is available on GitHub, where it is also possible to report issues and suggest improvements.

eugene is licensed under the MIT license.

Usage

Eugene has a number of subcommands, and can tell you about them:

$ eugene help
eugene is a tool for writing safer schema changes for PostgreSQL

eugene can run your migration scripts and detect which locks that is taken by each
individual SQL statement and summarize which operations that conflict with those
locks, in other words what the script must wait for and what
concurrent transactions that would be blocked.


Usage: eugene [COMMAND]

Commands:
  lint         Lint SQL migration script by analyzing syntax tree
  trace        Trace effects by running statements from SQL migration script
  modes        List postgres lock modes
  explain      Explain what operations a lock mode allows and conflicts with
  hints        Show migration hints that eugene can detect in traces
  completions  Generate shell completions for eugene
  help         Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help (see a summary with '-h')

  -V, --version
          Print version

The two main subcommands are eugene lint and eugene trace, which both have their own page. eugene lint will perform syntax tree analysis of your SQL script using the PostgreSQL parser, while eugene trace will actually run it in a transaction and inspect the effects of the script on the database. It will be easier to get started with eugene lint and it can catch many dangerous patterns, but it may also report some false positives and might not pick up everything that eugene trace can catch.

Hints provided by eugene

See hints for a list of hints that Eugene can give you.

Blog

I frequently blog about software development and other topics, here's blog posts about egene.

Release notes

The releases page is the best place to find release notes for eugene.

eugene lint

The eugene lint command will analyze the syntax tree of a SQL script and spot a number of dangerous patterns. It works by using the pg_query.rs library to parse SQL scripts using the same parser that PostgreSQL uses internally. This allows Eugene to work with the same kind of syntax trees that the server uses.

eugene lint will attempt to keep track of whether objects are new in the same transaction, so that it can avoid false positives for tables that aren't visible to other transactions yet. In some cases, it will report false positives, since it can't know the DDL of the tables that are being referenced. For example, it can't know if a type change is safe. It is easy to ignore these false positives by adding a comment to the SQL script, see ignores.

eugene lint can catch many things that it is specifically designed to catch, but it must have reasonably precise rules. Some SQL statements will implicitly create indexes, which will prevent writes to the table, and eugene lint will catch those that it knows about, but there may be some ways for this to happen that it doesn't know about.

If you want to run eugene lint in CI, or as a pre-commit hook, you can use --git-diff=main or -gmain to lint files that are new/unstaged, or have changes in them since main.

Usage

$ eugene help lint
Lint SQL migration script by analyzing syntax tree

`eugene lint` exits with failure if any lint is detected.

Usage: eugene lint [OPTIONS] [paths]...

Arguments:
  [paths]...
          Path to SQL migration scripts, directories, or '-' to read from stdin

Options:
  -v, --var <PLACEHOLDERS>
          Provide name=value for replacing ${name} with value in the SQL script
          
          Can be used multiple times to provide more placeholders.

  -i, --ignore <IGNORED_HINTS>
          Ignore the hints with these IDs, use `eugene hints` to see available hints
          
          Can be used multiple times: `-i E3 -i E4`
          
          Or comment your SQL statement like this:
          
          `-- eugene-ignore: E3, E4`
          
          alter table foo add column bar json;
          
          This will ignore hints E3 and E4 for this statement only.

  -f, --format <FORMAT>
          Output format, plain, json or markdown
          
          [default: plain]
          [possible values: json, markdown, md, plain]

  -a, --accept-failures
          Exit successfully even if problems are detected.
          
          Will still fail for syntax errors in the SQL script.

      --sort-mode <SORT_MODE>
          Sort mode for script discovery, auto, name or none
          
          This is used to order scripts when a path is a directory, or many paths are provided.
          
          `auto` will sort by versions or sequence numbers.
          
          `auto` requires all files to have the same naming scheme, either flyway-style or leading sequence numbers.
          
          `name` will sort lexically by name.
          
          [default: auto]
          [possible values: auto, name, none]

  -s, --skip-summary
          Skip the summary section for markdown output

  -g, --git-diff <GIT_DIFF>
          Filter out discovered scripts that are have not been changed since this git ref
          
          Pass a git ref, like a commit hash, tag, or branch name.

  -h, --help
          Print help (see a summary with '-h')

eugene trace

The eugene trace command will actually execute your SQL script in a transaction. If you have PostgreSQL installed, eugene trace can set up a temporary database server for you, and run through all the SQL scripts you give it to trace them. If you prefer to use your own database server, you can give provide eugene trace with connection information and it will roll back scripts by default, in this case.

PostgreSQL has a number of interesting features that can help eugene trace look for dangerous patterns while executing SQL statements in a transaction. eugene trace will look at the data types of every column, it will discover new indexes and constraints, and it will discover when database objects get moved to a new location on disk -- that is, table or index rewrites.

Since eugene trace has so much information, it is much less likely to trigger false positives than eugene lint, but it is also slower and requires a live database connection.

eugene trace can catch broad categories of dangerous patterns -- sometimes, it will discover a table rewrite that eugene lint can not detect, but it may not be able to tell you about why that table rewrite happened. eugene trace will discover all indexes and constraints created in a transaction, even if they were implicitly created. If you need to tell eugene trace that you know a statement to be safe, you can tell it to ignore a lint by adding a comment to your SQL script, see ignores.

If you want to run eugene trace in CI, or as a pre-commit hook, you can use --git-diff=main or -gmain to trace files that are new/unstaged, or have changes in them since main. eugene trace will still run all the scripts, but will only check the ones that have changed.

Usage

$ eugene help trace
Trace effects by running statements from SQL migration script

`eugene trace` will set up a temporary postgres server for tracing, unless disabled.

Reads $PGPASS for password to postgres, if ~/.pgpass is not found.

`eugene trace` exits with failure if any problems are detected.

Usage: eugene trace [OPTIONS] [paths]...

Arguments:
  [paths]...
          Path to SQL migration scripts, directories, or '-' to read from stdin

Options:
  -v, --var <PLACEHOLDERS>
          Provide name=value for replacing ${name} with value in the SQL script
          
          Can be used multiple times to provide more placeholders.

  -i, --ignore <IGNORED_HINTS>
          Ignore the hints with these IDs, use `eugene hints` to see available hints
          
          Can be used multiple times: `-i E3 -i E4`
          
          Or comment your SQL statement like this:
          
          `-- eugene-ignore: E3, E4`
          
          alter table foo add column bar json;
          
          This will ignore hints E3 and E4 for this statement only.

  -f, --format <FORMAT>
          Output format, plain, json or markdown
          
          [default: plain]
          [possible values: json, markdown, md, plain]

  -a, --accept-failures
          Exit successfully even if problems are detected.
          
          Will still fail for syntax errors in the SQL script.

      --sort-mode <SORT_MODE>
          Sort mode for script discovery, auto, name or none
          
          This is used to order scripts when a path is a directory, or many paths are provided.
          
          `auto` will sort by versions or sequence numbers.
          
          `auto` requires all files to have the same naming scheme, either flyway-style or leading sequence numbers.
          
          `name` will sort lexically by name.
          
          [default: auto]
          [possible values: auto, name, none]

  -s, --skip-summary
          Skip the summary section for markdown output

  -g, --git-diff <GIT_DIFF>
          Filter out discovered scripts that are have not been changed since this git ref
          
          Pass a git ref, like a commit hash, tag, or branch name.

      --disable-temporary
          Disable creation of temporary postgres server for tracing
          
          By default, trace will create a postgres server in a temporary directory
          
          This relies on having `initdb` and `pg_ctl` in PATH, which eugene images have.
          
          Eugene deletes the temporary database cluster when done tracing.

  -o, --postgres-options <POSTGRES_OPTIONS>
          Portgres options to pass to the temporary postgres server
          
          Example: `eugene trace -o "-c fsync=off -c log_statement=all"`
          
          [default: ]

      --initdb <INITDB_OPTIONS>
          Initdb options to pass when creating the temporary postgres server
          
          Example: `eugene trace --initdb "--encoding=UTF8"`
          
          Supply it more than once to add multiple options.

  -U, --user <USER>
          Username to use for connecting to postgres
          
          [default: postgres]

  -d, --database <DATABASE>
          Database to connect to
          
          [default: postgres]

  -H, --host <HOST>
          Host to connect to
          
          [default: localhost]

  -p, --port <PORT>
          Port to connect to
          
          [default: 5432]

  -c, --commit
          Commit at the end of the transaction.
          
          Commit is always enabled for the temporary server, otherwise rollback is default.

  -e, --extra
          Show locks that are normally not in conflict with application code

  -h, --help
          Print help (see a summary with '-h')

Ignoring rules

Both eugene lint and eugene trace can be instructed to ignore rules, so that false positives can be suppressed, or warnings that aren't relevant for your use case can be hidden.

You can ignore specific rule IDs for an entire transaction, using the command line flag:

eugene lint --ignore E2 my_script.sql
eugene trace --ignore E2 my_script.sql

You can ignore all rule IDs for a single statement:

-- eugene: ignore
alter table books alter column title set not null;

You can ignore specific rule IDs for a single statement:

-- eugene: ignore: E2, E3
alter table books alter column title set not null;

eugene web

Eugene has a tiny web API that can be used to lint SQL scripts. This API exposes some functionality for demo purposes only. It's running with very limited resources, so please be kind to it.

It is written using axum, mostly to learn something about how to write web APIs in Rust. You can check out the code in the eugene-web crate in the eugene repository.

Endpoints

All the endpoints are relative to https://kaveland.no/eugene/app or http://localhost:3000/eugene/app if you're running it locally.

POST /lint.html

This endpoint accepts a form with a parameter named sql that contains one or more SQL scripts preceeded by a -- name.sql marker. Each script can contain multiple statements. The API returns html. This endpoint is used by the form in the introduction page.

POST /lint.raw

This endpoint accepts a SQL script in the body and responds with a plain text response. It's suitable for use with curl from the terminal, you can check a file named dmo.sql like this:

curl -XPOST -d @dmo.sql https://kaveland.no/eugene/app/lint.raw
unnamed:1 E2 Validating table with a new `NOT NULL` column https://kaveland.no/eugene/hints/E2/
unnamed:1 E9 Taking dangerous lock without timeout https://kaveland.no/eugene/hints/E9/
unnamed:2 E2 Validating table with a new `NOT NULL` column https://kaveland.no/eugene/hints/E2/
unnamed:2 E4 Running more statements after taking `AccessExclusiveLock` https://kaveland.no/eugene/hints/E4/
unnamed:2 E9 Taking dangerous lock without timeout https://kaveland.no/eugene/hints/E9/
unnamed:2 W12 Multiple `ALTER TABLE` statements where one will do https://kaveland.no/eugene/hints/W12/

POST /lint.json

This endpoint accepts a json body:

{
  "sql": "-- name.sql\ncreate table books (id serial primary key);"
}

The sql member is a single SQL script. It responds with a json object that contains the results, suitable to use for rendering templates or something.

{
  "name": "dmo.sql",
  "passed_all_checks": false,
  "skip_summary": false  
  "statements": [
    {
      "statement_number": 1,
      "line_number": 1,
      "sql": "alter table books\n  alter column text set not null",
      "triggered_rules": [
        {
          "id": "E2",
          "name": "Validating table with a new `NOT NULL` column",
          "condition": "A column was changed from `NULL` to `NOT NULL`",
...          

Usage

If you find yourself using the API a lot, please consider installing eugene locally instead, or consider using my referral link to sponsor the hosting.

DigitalOcean Referral Badge

Running in GitHub Actions

With the --git-diff option, it is easy to set up eugene to run in a GitHub Actions workflow. Below are some example jobs that you copy to your github workflows. There are 4 different jobs configured:

  • trace will run eugene trace on the files that have changed since main and stop the build if it finds any issues.
  • lint will run eugene lint on the files that have changed since main and stop the build if it finds any issues.
  • post_trace will run eugene trace on the files that have changed since main and post the results as a markdown comment on the pull request, but allow the build to pass even if issues are found.
  • post_lint will run eugene lint on the files that have changed since main and post the results as a markdown comment on the pull request, but allow the build to pass even if issues are found.
name: Eugene CI check
on:
  pull_request:
    branches:
      - main
env:
  EUGENE_VERSION: "0.6.1"

permissions:
  contents: read
  pull-requests: write

jobs:
  trace:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v4
      with:
        fetch-depth: 0
    - name: Download eugene
      run: |
        curl -L  https://github.com/kaaveland/eugene/releases/download/$EUGENE_VERSION/eugene-x86_64-unknown-linux-musl -o eugene
        chmod +x eugene
    - name: Put postgres binaries on PATH for eugene
      run: echo "/usr/lib/postgresql/14/bin" >> $GITHUB_PATH
    - name: Trace
      run: ./eugene trace --git-diff origin/main migration-scripts

  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - name: Download eugene
        run: |
          curl -L  https://github.com/kaaveland/eugene/releases/download/$EUGENE_VERSION/eugene-x86_64-unknown-linux-musl -o eugene
          chmod +x eugene
      - name: Lint files
        run: ./eugene lint --git-diff origin/main migration-scripts

  post_trace:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - name: Download eugene
        run: |
          curl -L  https://github.com/kaaveland/eugene/releases/download/$EUGENE_VERSION/eugene-x86_64-unknown-linux-musl -o eugene
          chmod +x eugene
      - name: Put postgres binaries on PATH for eugene
        run: echo "/usr/lib/postgresql/14/bin" >> $GITHUB_PATH
      - name: Trace files
        run: ./eugene trace --git-diff origin/main migration-scripts -f md --accept-failures > trace.md
      - name: Post Comment
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: |
          COMMENT=$(cat trace.md)
          gh pr comment ${{ github.event.pull_request.number }} --body "$COMMENT"

  post_lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/checkout@v4
      - name: Download eugene
        run: |
          curl -L  https://github.com/kaaveland/eugene/releases/download/$EUGENE_VERSION/eugene-x86_64-unknown-linux-musl -o eugene
          chmod +x eugene
      - name: Lint files
        run: ./eugene lint --git-diff origin/main migration-scripts -f md --accept-failures > lint.md
      - name: Post Comment
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: |
          COMMENT=$(cat lint.md)
          gh pr comment ${{ github.event.pull_request.number }} --body "$COMMENT"

Running in GitLab CI

With the --git-diff option, it is easy to set up eugene to run in a GitLab CI/CD pipeline. Below are some example jobs that you can copy into your .gitlab-ci.yml file.

There are 6 different jobs configured:

  • lint will run eugene lint on the files that have changed since main and stop the build if it finds any issues.
  • trace will run eugene trace on the files that have changed since main and stop the build if it finds any issues.
  • trace_report and comment_trace will run eugene trace on the files that have changed since main and post the results as a markdown comment on the merge request, but allow the build to pass even if issues are found.
  • lint_report and comment_lint will run eugene lint on the files that have changed since main and post the results as a markdown comment on the merge request, but allow the build to pass even if issues are found.

Note that for comment_trace and comment_lint to work, GITLAB_TOKEN must be set in CI/CD Variables in the GitLab project settings. It should be a token that has access to the project, so that it can post comments on merge requests.

.eugene_rules:
  rules:
    - if: $CI_PIPELINE_SOURCE == 'merge_request_event'

.eugene:
  extends: .eugene_rules
  before_script:
    - git config --global --add safe.directory $CI_PROJECT_DIR
    - git fetch --depth=1 origin main
  image:
    name: ghcr.io/kaaveland/eugene:latest
    entrypoint: ["/bin/sh", "-c"]

lint:
  extends: .eugene
  script: eugene lint --git-diff origin/main migration-scripts

trace:
  extends: .eugene
  script: eugene trace --git-diff origin/main migration-scripts

trace_report:
    extends: .eugene
    script: eugene trace --git-diff origin/main migration-scripts -f md --accept-failures > trace.md
    artifacts:
      paths:
        - trace.md

comment_trace:
  extends: .eugene_rules
  image:
    name: registry.gitlab.com/gitlab-org/cli
    entrypoint: [ "/bin/sh", "-c" ]
  needs:
    - trace_report
  script:
    - body=$(cat trace.md)
    - glab mr note $CI_MERGE_REQUEST_IID --unique -m "$body"

lint_report:
  extends: .eugene
  script: eugene lint --git-diff origin/main migration-scripts -f md --accept-failures > lint.md
  rules:
    - if: $CI_PIPELINE_SOURCE == 'merge_request_event'
  artifacts:
    paths:
      - lint.md

comment_lint:
  extends: .eugene_rules
  image:
    name: registry.gitlab.com/gitlab-org/cli
    entrypoint: ["/bin/sh", "-c"]
  needs:
    - lint_report
  script:
    - body=$(cat lint.md)
    - glab mr note $CI_MERGE_REQUEST_IID --unique -m "$body"

eugene rules

This section contains a list of hints that eugene recognizes, whether they are supported by eugene lint, eugene trace, or both, and what you can do to avoid the dangerous pattern.

These are all automatically generated from templates during the build of eugene so when new hints are added, the documentation will be updated automatically.

Each chapter refers to a specific rule in the eugene codebase. The rule is identified by an ID that can be passed to eugene, as well as a name. The documentation describes what eugene looks for when triggering the rule, what effect the schema change may have on the database and concurrent transactions, and if there is a workaround. The documentation will tell you whether one or both of eugene lint and eugene trace that can detect the condition the rule describes.

Each rule page will link to some eugene example reports for the migration pattern it describes, so you can see what the output looks like.

E1 Validating table with a new constraint

Description

Triggered when: A new constraint was added and it is already VALID.

Effect: This blocks all table access until all rows are validated.

Workaround: Add the constraint as NOT VALID and validate it with ALTER TABLE ... VALIDATE CONSTRAINT later.

Detected by: eugene lint and eugene trace

Problematic migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
);

-- 2.sql
set local lock_timeout = '2s';
alter table authors
    add constraint name_not_null
        check (name is not null);

Safer migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
);

-- 2.sql
set local lock_timeout = '2s';
alter table authors
    add constraint name_not_null
        check (name is not null) not valid;

-- 3.sql
set local lock_timeout = '2s';
alter table authors
    validate constraint name_not_null;

Eugene report examples

E2 Validating table with a new NOT NULL column

Description

Triggered when: A column was changed from NULL to NOT NULL.

Effect: This blocks all table access until all rows are validated.

Workaround: Add a CHECK constraint as NOT VALID, validate it later, then make the column NOT NULL.

Detected by: eugene lint and eugene trace

Problematic migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
);

-- 2.sql
set local lock_timeout = '2s';
alter table authors
    alter column name set not null;

Safer migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
);

-- 2.sql
set local lock_timeout = '2s';
alter table authors
    add constraint check_name_not_null
        check (name is not null) not valid;

-- 3.sql
set local lock_timeout = '2s';
alter table authors
    validate constraint check_name_not_null;

-- 4.sql
set local lock_timeout = '2s';
-- eugene trace knows name has a valid not null check, but eugene lint doesn't
-- eugene: ignore E2
alter table authors
    alter name set not null;

Eugene report examples

E3 Add a new JSON column

Description

Triggered when: A new column of type json was added to a table.

Effect: This breaks SELECT DISTINCT queries or other operations that need equality checks on the column.

Workaround: Use the jsonb type instead, it supports all use-cases of json and is more robust and compact.

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,
    meta json
);

Safer migration

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null,
    meta jsonb
);

Eugene report examples

E4 Running more statements after taking AccessExclusiveLock

Description

Triggered when: A transaction that holds an AccessExclusiveLock started a new statement.

Effect: This blocks all access to the table for the duration of this statement.

Workaround: Run this statement in a new transaction.

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 column email text not null;
select count(*) from authors;

Safer 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 column email text not null;

-- 3.sql
select count(*) from authors;

Eugene report examples

E5 Type change requiring table rewrite

Description

Triggered when: A column was changed to a data type that isn't binary compatible.

Effect: This causes a full table rewrite while holding a lock that prevents all other use of the table.

Workaround: Add a new column, update it in batches, and drop the old column.

Detected by: eugene lint and eugene trace

Problematic migration

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
);

-- 2.sql
set local lock_timeout = '2s';
alter table prices
    alter price set data type bigint;

Safer migration

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
);

-- 2.sql
set local lock_timeout = '2s';
alter table prices
    add column new_price bigint;

-- 3.sql
update prices set new_price = price :: bigint;
set local lock_timeout = '2s';
alter table prices
    add constraint check_new_price_not_null
        check (new_price is not null) not valid;

-- 4.sql
set local lock_timeout = '2s';
alter table prices
    validate constraint check_new_price_not_null,
    drop column price;
-- eugene: ignore E4
-- this has to run in the same transaction as dropping the old price column
alter table prices
    rename column new_price to price;

Eugene report examples

E6 Creating a new index on an existing table

Description

Triggered when: A new index was created on an existing table without the CONCURRENTLY keyword.

Effect: This blocks all writes to the table while the index is being created.

Workaround: Run CREATE INDEX CONCURRENTLY instead of CREATE 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';
create index
    authors_name_idx on authors (name);

Safer migration

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
);

-- 2.sql
create index concurrently
    authors_name_idx on authors (name);

Eugene report examples

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;

Eugene report examples

E8 Creating a new exclusion constraint

Description

Triggered when: Found a new exclusion constraint.

Effect: This blocks all reads and writes to the table while the constraint index is being created.

Workaround: There is no safe way to add an exclusion constraint to an existing table.

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 authors_name_excl
        exclude (name with =);

Safer migration

Currently, we don't know of a safe way to avoid this issue.

Report an issue at the tracker if you know a way!

Eugene report examples

E9 Taking dangerous lock without timeout

Description

Triggered when: A lock that would block many common operations was taken without a timeout.

Effect: This can block all other operations on the table indefinitely if any other transaction holds a conflicting lock while idle in transaction or active.

Workaround: Run SET LOCAL lock_timeout = '2s'; before the statement and retry the migration if necessary.

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
alter table authors add column email text;

Safer 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 column email text;

Eugene report examples

E10 Rewrote table or index while holding dangerous lock

Description

Triggered when: A table or index was rewritten while holding a lock that blocks many operations.

Effect: This blocks many operations on the table or index while the rewrite is in progress.

Workaround: Build a new table or index, write to both, then swap them.

Detected by: eugene trace

Problematic migration

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
);

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 column meta jsonb;

-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint;

Safer migration

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
);

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 column meta jsonb;

-- 3.sql
set local lock_timeout = '2s';
-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint;

Eugene report examples

E11 Adding a SERIAL or GENERATED ... STORED column

Description

Triggered when: A new column was added with a SERIAL or GENERATED type.

Effect: This blocks all table access until the table is rewritten.

Workaround: Can not be done without a table rewrite.

Detected by: eugene lint

Problematic migration

-- 1.sql
create table prices (
    price int not null
);

-- 2.sql
set local lock_timeout = '2s';
alter table prices
    add column id serial;

Safer migration

Currently, we don't know of a safe way to avoid this issue.

Report an issue at the tracker if you know a way!

Eugene report examples

W12 Multiple ALTER TABLE statements where one will do

Description

Triggered when: Multiple ALTER TABLE statements targets the same table.

Effect: If the statements require table scans, there will be more scans than necessary.

Workaround: Combine the statements into one, separating the action with commas.

Detected by: eugene lint

Problematic migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email text
);

-- 2.sql
set lock_timeout = '2s';
alter table authors
    alter column name set not null;
-- eugene: ignore E2, E4
alter table authors
    alter column email set not null;

Safer migration

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email text
);

-- 2.sql
set lock_timeout = '2s';
-- eugene: ignore E2
alter table authors
  alter column name set not null,
  alter column email set not null;

Eugene report examples

W13 Creating an enum

Description

Triggered when: A new enum was created.

Effect: Removing values from an enum requires difficult migrations, and associating more data with an enum value is difficult.

Workaround: Use a foreign key to a lookup table instead.

Detected by: eugene lint

Problematic migration

-- 1.sql
create type document_type
    as enum ('invoice', 'receipt', 'other');
create table document (
    id int generated always as identity
        primary key,
    type document_type
);

Safer migration

-- 1.sql
create table document_type(
    type_name text primary key
);
insert into document_type
  values('invoice'), ('receipt'), ('other');
create table document (
    id int generated always as identity
        primary key,
    type text
        references document_type(type_name)
);

Eugene report examples

W14 Adding a primary key using an index

Description

Triggered when: A primary key was added using an index on the table.

Effect: This can cause postgres to alter the index columns to be NOT NULL.

Workaround: Make sure that all the columns in the index are already NOT NULL.

Detected by: eugene lint

Problematic migration

-- 1.sql
create table authors(
    name text
);

-- 2.sql
create unique index concurrently
    authors_name_key on authors(name);

-- 3.sql
set local lock_timeout = '2s';
alter table authors
    add constraint authors_name_pkey
        primary key using index authors_name_key;

Safer migration

-- 1.sql
create table authors(
    name text
);

-- 2.sql
create unique index concurrently
    authors_name_key on authors(name);

-- 3.sql
set local lock_timeout = '2s';
-- 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;

-- 4.sql
alter table authors
    add constraint authors_name_pkey
        primary key using index authors_name_key;

Eugene report examples

Appendix: Example Reports

The next section shows sample reports generated by eugene lint and eugene trace.

Eugene 🔒 lint report of examples/E1/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E1/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint name_not_null
        check (name is not null)

Lints

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.

Statement takes AccessExclusiveLock on public.authors, blocking reads until constraint name_not_null is validated.

Eugene 🔒 lint report of examples/E1/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E1/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint name_not_null
        check (name is not null) not valid

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E1/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    validate constraint name_not_null

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E1/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    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/E1/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add constraint name_not_null
        check (name is not null)

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

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 name_not_null of type CHECK 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 name_not_null which takes a lesser lock.

Eugene 🔒 trace report of examples/E1/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    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/E1/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add constraint name_not_null
        check (name is not null) not valid

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E1/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

alter table authors
    validate constraint name_not_null

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Eugene 🔒 lint report of examples/E2/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E2/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    alter column name set not null

Lints

Validating table with a new NOT NULL column

ID: E2

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.

Statement takes AccessExclusiveLock on public.authors by setting name to NOT NULL blocking reads until all rows are validated.

Eugene 🔒 lint report of examples/E2/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E2/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint check_name_not_null
        check (name is not null) not valid

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E2/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    validate constraint check_name_not_null

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E2/good/4.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 4.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

-- eugene trace knows name has a valid not null check, but eugene lint doesn't
-- eugene: ignore E2
alter table authors
    alter name set not null

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E2/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    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/E2/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    alter column name set not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Hints

Validating table with a new NOT NULL column

ID: E2

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 name in the table public.authors was changed to NOT NULL. If there is a CHECK (name IS NOT NULL) constraint on public.authors, this is safe. Splitting this kind of change into 3 steps can make it safe:

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

Eugene 🔒 trace report of examples/E2/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    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/E2/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add constraint check_name_not_null
        check (name is not null) not valid

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E2/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

alter table authors
    validate constraint check_name_not_null

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/E2/good/4.sql

Statement number 1 for 10ms

SQL

-- 4.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 trace knows name has a valid not null check, but eugene lint doesn't
-- eugene: ignore E2
alter table authors
    alter name set not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 lint report of examples/E3/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null,
    meta json
)

Lints

Add a new JSON column

ID: E3

A new column of type json was added to a table. This breaks SELECT DISTINCT queries or other operations that need equality checks on the column. A safer way is: Use the jsonb type instead, it supports all use-cases of json and is more robust and compact.

Created column meta with type json. The json type does not support equality and should not be used, use jsonb instead.

Eugene 🔒 lint report of examples/E3/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null,
    meta jsonb
)

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E3/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null,
    meta json
)

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/E3/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null,
    meta jsonb
)

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Eugene 🔒 lint report of examples/E4/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E4/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add column email text not null

No checks matched for this statement. ✅

Statement number 3

SQL

select count(*) from authors

Lints

Running more statements after taking AccessExclusiveLock

ID: E4

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.

Running more statements after taking AccessExclusiveLock.

Eugene 🔒 lint report of examples/E4/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E4/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add column email text not null

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E4/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
select count(*) from authors

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E4/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E4/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add column email text not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Statement number 3 for 10ms

SQL

select count(*) from authors

Locks at start

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

New locks taken

No new locks taken by this statement.

Hints

Running more statements after taking AccessExclusiveLock

ID: E4

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.authors, blocking all other transactions from accessing it.

Eugene 🔒 trace report of examples/E4/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E4/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add column email text not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E4/good/3.sql

Statement number 1 for 10ms

SQL

-- 3.sql
select count(*) from authors

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Eugene 🔒 lint report of examples/E5/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E5/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table prices
    alter price set data type bigint

Lints

Type change requiring table rewrite

ID: E5

A column was changed to a data type that isn't binary compatible. This causes a full table rewrite while holding a lock that prevents all other use of the table. A safer way is: Add a new column, update it in batches, and drop the old column.

Changed type of column price to pg_catalog.int8 in .prices. This operation requires a full table rewrite with AccessExclusiveLock if pg_catalog.int8 is not binary compatible with the previous type of price. Prefer adding a new column with the new type, then dropping/renaming..

Eugene 🔒 lint report of examples/E5/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E5/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table prices
    add column new_price bigint

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E5/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
update prices set new_price = price :: bigint

No checks matched for this statement. ✅

Statement number 2

SQL

set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 3

SQL

alter table prices
    add constraint check_new_price_not_null
        check (new_price is not null) not valid

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E5/good/4.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 4.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table prices
    validate constraint check_new_price_not_null,
    drop column price

No checks matched for this statement. ✅

Statement number 3

SQL

-- eugene: ignore E4
-- this has to run in the same transaction as dropping the old price column
alter table prices
    rename column new_price to price

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E5/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

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/E5/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table prices
    alter price set data type bigint

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110
publicpricesShareLockTable110
publicprices_pkeyAccessExclusiveLockIndex110

Hints

Type change requiring table rewrite

ID: E5

A column was changed to a data type that isn't binary compatible. This causes a full table rewrite while holding a lock that prevents all other use of the table. A safer way is: Add a new column, update it in batches, and drop the old column.

The column price in the table public.prices was changed from type int4 to int8. This requires an AccessExclusiveLock that will block all other transactions from using the table while it is being rewritten.

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.prices. The index was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Rewrote table or index while holding dangerous lock

ID: E10

A table or index was rewritten while holding a lock that blocks many operations. This blocks many operations on the table or index while the rewrite is in progress. A safer way is: Build a new table or index, write to both, then swap them.

The Table public.prices was rewritten while holding AccessExclusiveLock on the Table public.prices. This blocks SELECT, FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, UPDATE, DELETE, INSERT, MERGE while the rewrite is in progress.

Eugene 🔒 trace report of examples/E5/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

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/E5/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table prices
    add column new_price bigint

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E5/good/3.sql

Statement number 1 for 10ms

SQL

-- 3.sql
update prices set new_price = price :: bigint

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

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 3 for 10ms

SQL

alter table prices
    add constraint check_new_price_not_null
        check (new_price is not null) not valid

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E5/good/4.sql

Statement number 1 for 10ms

SQL

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

alter table prices
    validate constraint check_new_price_not_null,
    drop column price

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110

Statement number 3 for 10ms

SQL

-- eugene: ignore E4
-- this has to run in the same transaction as dropping the old price column
alter table prices
    rename column new_price to price

Locks at start

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110

New locks taken

No new locks taken by this statement.

Eugene 🔒 lint report of examples/E6/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E6/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

create index
    authors_name_idx on authors (name)

Lints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

Statement takes ShareLock on public.authors, blocking writes while creating index public.authors_name_idx.

Eugene 🔒 lint report of examples/E6/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E6/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
create index concurrently
    authors_name_idx on authors (name)

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E6/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E6/bad/2.sql

Statement number 1 for 10ms

SQL

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

create index
    authors_name_idx on authors (name)

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsShareLockTable110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.authors. The index public.authors_name_idx was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Eugene 🔒 trace report of examples/E6/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E6/good/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
create index concurrently
    authors_name_idx 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 🔒 lint report of examples/E7/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E7/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint unique_name unique(name)

Lints

Creating a new unique constraint

ID: E7

Adding a new unique constraint implicitly creates index. This blocks all writes to the table while the index is being created and validated. A safer way is: CREATE UNIQUE INDEX CONCURRENTLY, then add the constraint using the index.

New constraint unique_name creates implicit index on public.authors, blocking writes until index is created and validated.

Eugene 🔒 lint report of examples/E7/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E7/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
create unique index concurrently
    authors_name_unique on authors(name)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E7/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint unique_name
        unique using index authors_name_unique

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E7/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E7/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add constraint unique_name unique(name)

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110
publicauthorsShareLockTable110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.authors. The index public.unique_name was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Creating a new unique constraint

ID: E7

Adding a new unique constraint implicitly creates index. This blocks all writes to the table while the index is being created and validated. A safer way is: CREATE UNIQUE INDEX CONCURRENTLY, then add the constraint using the index.

A new unique constraint unique_name was added to the table public.authors. This constraint creates a unique index on the table, and blocks all writes. Consider creating the index concurrently in a separate transaction, then adding the unique constraint by using the index: ALTER TABLE public.authors ADD CONSTRAINT unique_name UNIQUE USING INDEX public.unique_name;

Eugene 🔒 trace report of examples/E7/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E7/good/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
create unique index concurrently
    authors_name_unique 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/E7/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

alter table authors
    add constraint unique_name
        unique using index authors_name_unique

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 lint report of examples/E8/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E8/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint authors_name_excl
        exclude (name with =)

Lints

Creating a new exclusion constraint

ID: E8

Found a new exclusion constraint. This blocks all reads and writes to the table while the constraint index is being created. A safer way is: There is no safe way to add an exclusion constraint to an existing table.

Statement takes AccessExclusiveLock on public.authors, blocking reads and writes until constraint authors_name_excl is validated and has created index.

Eugene 🔒 trace report of examples/E8/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E8/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add constraint authors_name_excl
        exclude (name with =)

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110
publicauthorsShareLockTable110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.authors. The index public.authors_name_excl was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Creating a new exclusion constraint

ID: E8

Found a new exclusion constraint. This blocks all reads and writes to the table while the constraint index is being created. A safer way is: There is no safe way to add an exclusion constraint to an existing table.

A new exclusion constraint authors_name_excl was added to the table public.authors. There is no safe way to add an exclusion constraint to an existing table. This constraint creates an index on the table, and blocks all reads and writes.

Eugene 🔒 lint report of examples/E9/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E9/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
alter table authors add column email text

Lints

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.

Statement takes lock on public.authors, but does not set a lock timeout.

Eugene 🔒 lint report of examples/E9/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E9/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors add column email text

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E9/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E9/bad/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
alter table authors add column email text

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Hints

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.

Eugene 🔒 trace report of examples/E9/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E9/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors add column email text

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 lint report of examples/E10/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

No checks matched for this statement. ✅

Statement number 2

SQL

create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E10/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors add column meta jsonb

No checks matched for this statement. ✅

Statement number 3

SQL

-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E10/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)

No checks matched for this statement. ✅

Statement number 2

SQL

create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E10/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add column meta jsonb

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E10/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/E10/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int 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 10ms

SQL

create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E10/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors add column meta jsonb

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Statement number 3 for 10ms

SQL

-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint

Locks at start

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110
publicpricesShareLockTable110
publicprices_pkeyAccessExclusiveLockIndex110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.prices. The index was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Rewrote table or index while holding dangerous lock

ID: E10

A table or index was rewritten while holding a lock that blocks many operations. This blocks many operations on the table or index while the rewrite is in progress. A safer way is: Build a new table or index, write to both, then swap them.

The Table public.prices was rewritten while holding AccessExclusiveLock on the Table public.authors. This blocks SELECT, FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, UPDATE, DELETE, INSERT, MERGE while the rewrite is in progress.

Eugene 🔒 trace report of examples/E10/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int 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 10ms

SQL

create table authors (
    id integer generated always as identity
        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.

Eugene 🔒 trace report of examples/E10/good/2.sql

Statement number 1 for 10ms

SQL

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

alter table authors
    add column meta jsonb

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 trace report of examples/E10/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 E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110
publicpricesShareLockTable110
publicprices_pkeyAccessExclusiveLockIndex110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.prices. The index was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Rewrote table or index while holding dangerous lock

ID: E10

A table or index was rewritten while holding a lock that blocks many operations. This blocks many operations on the table or index while the rewrite is in progress. A safer way is: Build a new table or index, write to both, then swap them.

The Table public.prices was rewritten while holding AccessExclusiveLock on the Table public.prices. This blocks SELECT, FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, UPDATE, DELETE, INSERT, MERGE while the rewrite is in progress.

Eugene 🔒 lint report of examples/E11/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table prices (
    price int not null
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/E11/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table prices
    add column id serial

Lints

Adding a SERIAL or GENERATED ... STORED column

ID: E11

A new column was added with a SERIAL or GENERATED type. This blocks all table access until the table is rewritten. A safer way is: Can not be done without a table rewrite.

Added column id with type that will force table rewrite in .prices. serial types and GENERATED ALWAYS as ... STORED columns require a full table rewrite with AccessExclusiveLock.

Eugene 🔒 trace report of examples/E11/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table prices (
    price int not null
)

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/E11/bad/2.sql

Statement number 1 for 10ms

SQL

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

alter table prices
    add column id serial

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicpricesAccessExclusiveLockTable110
publicpricesShareLockTable110

Hints

Creating a new index on an existing table

ID: E6

A new index was created on an existing table without the CONCURRENTLY keyword. This blocks all writes to the table while the index is being created. A safer way is: Run CREATE INDEX CONCURRENTLY instead of CREATE INDEX.

A new index was created on the table public.prices. The index was created non-concurrently, which blocks all writes to the table. Use CREATE INDEX CONCURRENTLY to avoid blocking writes.

Rewrote table or index while holding dangerous lock

ID: E10

A table or index was rewritten while holding a lock that blocks many operations. This blocks many operations on the table or index while the rewrite is in progress. A safer way is: Build a new table or index, write to both, then swap them.

The Table public.prices was rewritten while holding AccessExclusiveLock on the Table public.prices. This blocks SELECT, FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, UPDATE, DELETE, INSERT, MERGE while the rewrite is in progress.

Eugene 🔒 lint report of examples/W12/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W12/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 2.sql
set lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    alter column name set not null

No checks matched for this statement. ✅

Statement number 3

SQL

-- eugene: ignore E2, E4
alter table authors
    alter column email set not null

Lints

Multiple ALTER TABLE statements where one will do

ID: W12

Multiple ALTER TABLE statements targets the same table. If the statements require table scans, there will be more scans than necessary. A safer way is: Combine the statements into one, separating the action with commas.

Multiple ALTER TABLE statements on public.authors. Combine them into a single statement to avoid scanning the table multiple times..

Eugene 🔒 lint report of examples/W12/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W12/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
set lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

-- eugene: ignore E2
alter table authors
  alter column name set not null,
  alter column email set not null

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/W12/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email 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/W12/bad/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
set 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

alter table authors
    alter column name set not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Hints

Validating table with a new NOT NULL column

ID: E2

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 name in the table public.authors was changed to NOT NULL. If there is a CHECK (name IS NOT NULL) constraint on public.authors, this is safe. Splitting this kind of change into 3 steps can make it safe:

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

Statement number 3 for 10ms

SQL

-- eugene: ignore E2, E4
alter table authors
    alter column email set not null

Locks at start

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

New locks taken

No new locks taken by this statement.

Eugene 🔒 trace report of examples/W12/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email 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/W12/good/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
set 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
alter table authors
  alter column name set not null,
  alter column email set not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Eugene 🔒 lint report of examples/W13/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 1.sql
create type document_type
    as enum ('invoice', 'receipt', 'other')

Lints

Creating an enum

ID: W13

A new enum was created. Removing values from an enum requires difficult migrations, and associating more data with an enum value is difficult. A safer way is: Use a foreign key to a lookup table instead.

Created enum document_type. Enumerated types are not recommended for use in new applications. Consider using a foreign key to a lookup table instead..

Statement number 2

SQL

create table document (
    id int generated always as identity
        primary key,
    type document_type
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W13/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table document_type(
    type_name text primary key
)

No checks matched for this statement. ✅

Statement number 2

SQL

insert into document_type
  values('invoice'), ('receipt'), ('other')

No checks matched for this statement. ✅

Statement number 3

SQL

create table document (
    id int generated always as identity
        primary key,
    type text
        references document_type(type_name)
)

No checks matched for this statement. ✅

Eugene 🔒 trace report of examples/W13/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create type document_type
    as enum ('invoice', 'receipt', 'other')

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

create table document (
    id int generated always as identity
        primary key,
    type document_type
)

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/W13/good/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table document_type(
    type_name text primary key
)

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

insert into document_type
  values('invoice'), ('receipt'), ('other')

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Statement number 3 for 10ms

SQL

create table document (
    id int generated always as identity
        primary key,
    type text
        references document_type(type_name)
)

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Eugene 🔒 lint report of examples/W14/bad/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W14/bad/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
create unique index concurrently
    authors_name_key on authors(name)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W14/bad/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

SQL

alter table authors
    add constraint authors_name_pkey
        primary key using index authors_name_key

Lints

Adding a primary key using an index

ID: W14

A primary key was added using an index on the table. This can cause postgres to alter the index columns to be NOT NULL. A safer way is: Make sure that all the columns in the index are already NOT NULL.

New primary key constraint using index on public.authors, may cause postgres to SET NOT NULL on columns in the index. This lint may be a false positive if the columns are already NOT NULL, ignore it by commenting the statement with -- eugene: ignore: W14.

Eugene 🔒 lint report of examples/W14/good/1.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 1.sql
create table authors(
    name text
)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W14/good/2.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 2.sql
create unique index concurrently
    authors_name_key on authors(name)

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W14/good/3.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script passed all the checks ✅

Statement number 1

SQL

-- 3.sql
set local lock_timeout = '2s'

No checks matched for this statement. ✅

Statement number 2

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

No checks matched for this statement. ✅

Eugene 🔒 lint report of examples/W14/good/4.sql

This is a human readable SQL script safety report generated by eugene. Keep in mind that lints can be ignored by adding a -- eugene: ignore E123 comment to the SQL statement or by passing --ignore E123 on the command line.

The migration script did not pass all the checks ❌

Statement number 1

SQL

-- 4.sql
alter table authors
    add constraint authors_name_pkey
        primary key using index authors_name_key

Lints

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.

Statement takes lock on public.authors, but does not set a lock timeout.

Adding a primary key using an index

ID: W14

A primary key was added using an index on the table. This can cause postgres to alter the index columns to be NOT NULL. A safer way is: Make sure that all the columns in the index are already NOT NULL.

New primary key constraint using index on public.authors, may cause postgres to SET NOT NULL on columns in the index. This lint may be a false positive if the columns are already NOT NULL, ignore it by commenting the statement with -- eugene: ignore: W14.

Eugene 🔒 trace report of examples/W14/bad/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/bad/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/bad/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

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

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

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.

Validating table with a new NOT NULL column

ID: E2

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 name in the table public.authors was changed to NOT NULL. If there is a CHECK (name IS NOT NULL) constraint on public.authors, this is safe. Splitting this kind of change into 3 steps can make it safe:

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

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

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

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

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

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.