skip to content

When psql runs a migration file with -f and a statement in the middle fails, what happens by default, and how do ON_ERROR_STOP and --single-transaction change it?

level: middleimportance: must knowfreq 60%

answer

  1. the default is to keep going
  2. exit status 3
  3. BEGIN before, COMMIT after
  4. an aborted transaction ignores the rest
  5. CONCURRENTLY cannot join in

basics

~20 s

By default psql prints the error, runs the rest of the file and exits 0. ON_ERROR_STOP=1 stops at the first error with exit status 3; -1 wraps the file in BEGIN/COMMIT, sending ROLLBACK instead when ON_ERROR_STOP catches a failure.

solid answer

~40 s

Out of the box psql prints the error, carries on with the next statement and, for a `-f` file, still exits with status 0, so a CI job reports success after a half-applied migration. `-v ON_ERROR_STOP=1` makes psql stop at the first failing statement and exit with status 3, which a pipeline can detect. `-1` (`--single-transaction`) sends `BEGIN` before the file and `COMMIT` after it; with ON_ERROR_STOP set, a failure sends `ROLLBACK` instead, so either everything applies or nothing does. Without ON_ERROR_STOP, `-1` still applies nothing, because the remaining statements fail with `current transaction is aborted` and the final `COMMIT` becomes a rollback, but the exit status is 0. The file must not contain its own `BEGIN`/`COMMIT`, nor commands such as `CREATE INDEX CONCURRENTLY` that cannot run inside a transaction block.

code

bash · 6 lines
bash
psql -X -v ON_ERROR_STOP=1 --single-transaction \
     -d "$DATABASE_URL" -f migrations/0042_parcel_status.sql
echo "psql exit status: $?"

psql -X -v ON_ERROR_STOP=1 \
     -d "$DATABASE_URL" -f migrations/0042b_parcel_status_index_concurrently.sql

go deeper

for a junior

Know that psql keeps going after an error unless you set ON_ERROR_STOP, and that -1 runs a whole file as one transaction.

for a middle

Explain the exit statuses, what -1 actually sends, why ON_ERROR_STOP and -1 belong together, and which commands refuse to run inside a transaction block.

for a senior

Show a deploy-grade invocation: -X, ON_ERROR_STOP, -1, separate non-transactional steps, and a pipeline that treats exit status 3 as a failed migration rather than reading logs.

for a principal

Make atomic, fail-fast migrations a team standard enforced in CI, and decide how non-transactional steps such as concurrent index builds are sequenced, retried and reviewed.

## The default: report the error and keep going **psql** treats a script like a stream of typed commands. When a statement fails, it prints the server's error and moves on to the next statement. In **autocommit** mode (the default, `AUTOCOMMIT` on) every statement that succeeded before the failure has already committed, and every statement after it still runs. When the file ends, psql exits with **status 0**, because finishing the file counts as finishing normally. For a migration script run by a deploy pipeline that is the worst combination: a schema that is partly migrated, and a job that reports success. ## ON_ERROR_STOP: stop, and say so in the exit status Setting the psql variable **`ON_ERROR_STOP`** (`-v ON_ERROR_STOP=1` on the command line, or `\set ON_ERROR_STOP on` at the top of the file) changes this: psql stops at the first error, abandons the current script and any script it included, and exits with a distinct status. | Exit status | Meaning | |---|---| | `0` | psql finished normally, which includes a script whose statements failed without ON_ERROR_STOP | | `1` | a fatal error of psql's own, such as a missing file or out of memory | | `2` | the connection went bad in a non-interactive session | | `3` | an error occurred in a script and `ON_ERROR_STOP` was set | A CI step can therefore test for a non-zero status. ON_ERROR_STOP on its own does not undo anything: statements that committed before the failure stay committed. ## --single-transaction: all or nothing **`-1`** (`--single-transaction`) works only with `-c` or `-f`. psql: 1. sends `BEGIN` before the first `-c` or `-f` action; 2. runs every statement of the file inside that one transaction; 3. sends `COMMIT` after the last action, or `ROLLBACK` if a step failed and `ON_ERROR_STOP` is set. Without ON_ERROR_STOP the result is still atomic, but by accident. After the first error the transaction is **aborted**; every later statement fails with `current transaction is aborted, commands ignored until end of transaction block` (SQLSTATE `25P02`), and the final `COMMIT` is answered with `ROLLBACK`. Nothing is applied, yet psql exits 0, and the log is full of errors that are only echoes of the first one. **Use both flags together.** ## What does not fit inside one transaction - **Explicit transaction control in the file.** If the script has its own `BEGIN`, `COMMIT` or `ROLLBACK`, `-1` does not have the intended effect; pick one mechanism. - **Commands that refuse a transaction block** fail with `... cannot run inside a transaction block` (SQLSTATE `25001`): `CREATE INDEX CONCURRENTLY`, `REINDEX ... CONCURRENTLY`, `VACUUM`, `CREATE DATABASE`, `ALTER SYSTEM`. Under `-1`, one of them makes the whole transaction fail. - **`ON_ERROR_ROLLBACK`** wraps each statement in a savepoint so an error does not abort the transaction. That is handy interactively but defeats atomicity in a script; its default is `off`, so leave it there. ## Reading the failure afterwards A failed deploy is only half handled until someone can tell which statement broke. A few psql habits make the log useful: - **Prefer `-f` to `< file`.** With `-f`, psql reports errors with the file name and line number, which input redirection does not give you. - **Echo the input.** `-a` (the same as `\set ECHO all`) prints each input line as it is read, so the failing statement sits right above its error in the CI log. - **Trust the first error.** Under `-1` without ON_ERROR_STOP, every later `current transaction is aborted` message is a consequence; the first `ERROR` is the cause. - **In an interactive session**, `\errverbose` repeats the most recent error at maximum verbosity, including the SQLSTATE and the source location in the server. ## A safe invocation for CI ```bash psql -X -v ON_ERROR_STOP=1 --single-transaction \ -d "$DATABASE_URL" -f migrations/0042_parcel_status.sql ``` - `-X` skips `psqlrc` and `~/.psqlrc`, so nothing in the runner's home directory changes how the script behaves. - `ON_ERROR_STOP=1` turns the first failure into exit status 3. - `--single-transaction` makes the migration atomic. Put non-transactional steps, such as a concurrent index build, in a separate invocation without `-1`, still with `ON_ERROR_STOP`.

  • Why do careful teams also pass -X to psql in CI?
    `-X` (`--no-psqlrc`) skips the system `psqlrc` and the user's `~/.psqlrc`. Without it, whatever the runner's account has there, such as `\timing`, `\set AUTOCOMMIT off` or output settings, runs before the script and can change its behaviour or its output. A deploy script should depend only on the flags on its own command line.
  • How do you ship a migration that needs CREATE INDEX CONCURRENTLY?
    Split it. Run the transactional part with `-1` and `ON_ERROR_STOP`, then run the concurrent build as its own psql invocation without `-1`, so the statement autocommits on its own, still with `ON_ERROR_STOP`. A failed concurrent build leaves an invalid index that must be dropped before a retry; that clean-up is index upkeep rather than psql.

  • Believing psql exits non-zero whenever a statement in a -f script fails
  • Saying --single-transaction on its own makes psql stop at the first error
  • Turning on ON_ERROR_ROLLBACK to make a migration script atomic
  • Using -1 on a file that already contains its own BEGIN and COMMIT
  • Putting CREATE INDEX CONCURRENTLY inside a single-transaction migration