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.

Demo

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.

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.

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

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 ✅

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
`public`	`prices`	`AccessExclusiveLock`	Table	1	❌	10
`public`	`prices`	`ShareLock`	Table	1	❌	10
`public`	`prices_pkey`	`AccessExclusiveLock`	Index	1	❌	10

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
`public`	`authors`	`AccessExclusiveLock`	Table	1	❌	10
`public`	`authors`	`ShareLock`	Table	1	❌	10

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
`public`	`authors`	`AccessExclusiveLock`	Table	1	❌	10
`public`	`authors`	`ShareLock`	Table	1	❌	10

Schema	Object	Mode	Relkind	OID	Safe	Duration held (ms)
`public`	`prices`	`AccessExclusiveLock`	Table	1	❌	10
`public`	`prices`	`ShareLock`	Table	1	❌	10
`public`	`prices_pkey`	`AccessExclusiveLock`	Index	1	❌	10

Eugene Documentation