September 25, 20269 min read

Ptah 0.8.1: The Online Migration Takes a Lock Timeout

Ptah 0.8.1 runs the generated online migration under a lock timeout, and ptah-compat now applies and lints an Atlas project on TimescaleDB.

Nathan BrooksTechnical Writer

Ptah 0.8.1 is published on GitHub. It is a patch release, and most of it falls into two groups.

The first group finishes the online path from 0.8.0. The 0.8.0 post ended its first section with a conflict. The migration that diff.online_alter generates could not take a lock timeout, and the online mode would not apply it without one. In 0.8.1 the same migration applies under the online mode with a lock timeout, and the default lint rules no longer warn about it.

The second group is about Atlas projects on TimescaleDB. We ran the migration history of an open-source Atlas project whose CI creates the timescaledb extension before it applies anything. ptah-compat 0.8.0 refused to migrate that database, could not lint against a dev database like it, and recorded revision versions differently from Atlas. 0.8.1 fixes all three.

The examples ran with the official 0.8.1 binaries on PostgreSQL 18.6, and on TimescaleDB 2.30.1 over PostgreSQL 18.6. Each comparison with 0.8.0 ran the same command with the official 0.8.0 binaries. The example files hold the fixtures, the full output and the checksums.

The generated migration runs under the online mode

The example is the one from the 0.8.0 post: an accounts table, a CHECK constraint added to the desired schema, and diff.online_alter: true in ptah.yaml. ptah migrations generate wrote the same file as before. It adds the constraint NOT VALID, then validates it, and it is marked no_transaction so that the two statements commit separately.

In 0.8.0 the default rules reported PG305 on the NOT VALID line, although that is the form the rule itself recommends. In 0.8.1 the default rules and online: require both report nothing:

ptah migrations lint --dir migrations --dialect postgres --latest 1

No lint findings.

PG306, the same advice for a foreign key, had the same defect and got the same fix.

With online: require in the directory’s .ptah-lint.yaml, migrations up still refuses to run without a lock timeout, as it did in 0.8.0:

error: online mode requires a lock timeout on PostgreSQL: a statement that takes ACCESS EXCLUSIVE for an instant still queues behind any conflicting lock, and every later reader and writer of that table queues behind it. Set --lock-timeout, or migration.lock_timeout in the project config

The difference is what happens when you give it one. In 0.8.0 the file refused --lock-timeout 3s, because a no_transaction migration took no timeout at all. In 0.8.1 the dry run passes, and it shows how the timeout reaches a migration that runs outside a transaction: SET lock_timeout = '3000ms' on the session before the first statement, and RESET lock_timeout after the last. Apply migrations explains why this is a session setting.

--tx-mode none is no longer a way around the mode. In 0.8.0 the dry run passed with it, because it dropped the timeout requirement. In 0.8.1 it is refused with the same message as a run with no timeout.

What the timeout does when the table is busy

We repeated the lock-queue measurement from the 0.8.0 post, this time with the timeout set. One session held the table in an open transaction for ten seconds. Two seconds later, migrations up --lock-timeout 3s started. While it waited, pg_stat_activity listed its ALTER TABLE ... ADD CONSTRAINT waiting on a lock, and a SELECT sent to the same table queued behind it.

The migration gave up after three seconds:

error: error running migrations: failed to apply migration 1790317351: failed to execute migration SQL: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
SQL: ALTER TABLE "accounts" ADD CONSTRAINT "accounts_plan_known" CHECK (plan IN ('monthly', 'annual', 'trial')) NOT VALID

The migration log records the attempt from 06:22:43.090 to 06:22:46.101 UTC. The queued SELECT returned after 544 ms. It was released when the migration gave up, about five seconds before the blocking transaction ended. In the 0.8.0 run there was no timeout: the migration waited about eight seconds, and a SELECT with a two-second lock timeout gave up behind it.

A timeout turns a long wait into a failed run, and the run has to be finished by someone. migrations status says what state the failure left:

=== MIGRATION STATUS ===
Database: ***
Dialect: postgres
Schema: public

Current Version: 1790100000
Total Migrations: 2
Applied Migrations: 1
Pending Migrations: 1
Out-of-order Migrations: 0
Status: ❌ Dirty migration state detected
Dirty Migration: version=1790317351 state=failed direction=up applied=0/2
Error: failed to execute migration SQL: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
SQL: ALTER TABLE "accounts" ADD CONSTRAINT "accounts_plan_known" CHECK (plan IN ('monthly', 'annual', 'trial')) NOT VALID
Error Statement: ALTER TABLE "accounts" ADD CONSTRAINT "accounts_plan_known" CHECK (plan IN ('monthly', 'annual', 'trial')) NOT VALID

No statement of this migration reached the database. Run 'ptah migrations up --allow-dirty' to apply it.

A plain retry is refused, because the revision is marked as failed. After the blocking transaction ended, the retry with --allow-dirty applied the migration:

ptah migrations up --db-url "$DATABASE_URL" --migrations-dir migrations --verify-sum --lock-timeout 3s --allow-dirty

Afterwards schema compare --exit-code and migrations status --exit-code both exited 0, and pg_constraint showed the constraint validated.

The timeout limits the wait for a lock. It does not limit how long a statement holds the lock once it has it. The failure here was simple to recover from because the first statement never ran. A CREATE INDEX CONCURRENTLY stopped by the same timeout leaves an invalid index behind, and the retry needs that index dropped first. Change a schema without downtime walks through that case. We did not run it for this post.

Atlas projects on TimescaleDB

The fixture is a small Atlas directory in the shape we met in that project: 001_events.sql creates a table and makes it a hypertable, 002_events_kind.sql adds an index, and Atlas CE v1.3.0 wrote the atlas.sum. The database had CREATE EXTENSION timescaledb run before any migration, as that project’s CI does.

ptah-compat 0.8.0 refused to migrate it:

Error: sql/migrate: connected database is not clean: found schema "_timescaledb_cache". baseline version or allow-dirty is required

The schema belongs to the extension, not to the user. Atlas does not count an extension’s schemas and tables when it decides whether a database is clean, and it does not count partitions (#3536 has the measurements). ptah-compat 0.8.1 reads the database the same way, and applies both migrations:

ptah-compat migrate apply --dir file://migrations --url "$DATABASE_URL"

Migrating to version 002 from 2 pending migrations.
Migration complete. Current version: 002

The revision table now spells each version the way its file name does:

 version | applied
---------+---------
 001     |       2
 002     |       1
(2 rows)

0.8.0 recorded 1 and 2 in the same column. Atlas records the digits as written, and compares versions as text, so on that history Atlas CE reported the current version as 2 rather than 002. On the longer history in #3532, the same difference made Atlas refuse the next file as out of order. On the database 0.8.1 migrated, Atlas CE v1.3.0 reads the history as its own:

Migration Status: OK
  -- Current Version: 002
  -- Next Version:    Already at latest version
  -- Executed Files:  2
  -- Pending Files:   0

The native command, ptah migrations up --dir-format atlas --revision-format atlas, now records the same rows in the same place. In 0.8.0 it kept the table in the connection’s schema. Atlas keeps it in a schema named atlas_schema_revisions when the URL pins no search_path, so each tool read the other’s database as never migrated (#3539). On the history the 0.8.1 native command wrote, Atlas CE reports OK with nothing pending.

Linting against a dev database failed in 0.8.0. migrate lint --dev-url empties the dev database before it replays the directory. 0.8.0 planned to drop the timescaledb extension and then to alter a table in one of the schemas that drop removes, so the cleanup failed (full output). 0.8.1 keeps the extensions a dev database already had (#3545). The same migrate lint --dev-url ... --latest 2 replayed both migrations and exited 0, and timescaledb was still installed in the dev database afterwards. Atlas CE v1.3.0 also exits 0 on this directory, and its dev database no longer had the extension afterwards.

Upgrading

online: require needs a lock timeout in every transaction mode. --tx-mode none and a directory of no_transaction files no longer exempt a run. Set --lock-timeout or migration.lock_timeout.
A timeout on a dialect without one is refused in every transaction mode. On SQLite, ClickHouse and the other dialects that have no timeout setting, a migration that declares one is refused before any SQL runs (#3528; we did not run this case).
Both lint verbs write the report to standard output, whatever the outcome and format, and notes to standard error (#3530). The exit code carries the verdict. In 0.8.0 a failing migrations lint --format json wrote the report to standard error and a note to standard output, so a caller that redirected standard output got the note on exactly the run with findings.
The native Atlas revision table moves. With --revision-format atlas on a PostgreSQL URL that pins no search_path, ptah migrations reads and writes atlas_schema_revisions.atlas_schema_revisions. A table 0.8.0 left in public is refused, not replayed. The refusal is quoted below the list.
An Atlas history ptah-compat 0.8.0 wrote needs nothing. Its 1 and 2 still match 001 and 002: migrate status in 0.8.1 reported nothing pending on it, and migrate apply had nothing to execute.
A dev database keeps its extensions. Replay against --dev-url leaves the extensions that were installed before the run.

On a database where 0.8.0 ran ptah migrations up --revision-format atlas, ptah migrations status in 0.8.1 stops before it reads anything:

error: error getting migration status: failed to get migration revisions: failed to initialize migrations table: found revision table "atlas_schema_revisions" in schema "public", and none in schema "atlas_schema_revisions", where Atlas keeps it for a URL that pins no search_path and where this run reads it: pass --migrations-schema public to keep using the table where it is, or move it into schema "atlas_schema_revisions"

If your file names are zero-padded, read the first known issue below before you pass --migrations-schema public.

Also in this release

At a terminal, ptah --help and ptah help now draw the same wordmark as a bare ptah; captured or piped output is unchanged (#3531). The documentation site has a version picker with release dates, a latest badge and a filter (#3513). The rest of the release is dependency updates, listed on the release page.

Known issues

We found two defects in 0.8.1 while preparing this post, and filed both.

migrations status reports a 0.8.0 native Atlas history as missing and pending at once (#3550). After --migrations-schema public, the rows 0.8.0 wrote as 1 and 2 show up as applied migrations with no file, and status --exit-code exits 1. migrations up on the same database applies nothing, as it should. The issue has the measurement and a workaround: rewrite the version column to the digits the file names spell.
PG101 advises CREATE INDEX CONCURRENTLY on a hypertable (#3551). The 0.8.1 lint run above reports it on 002_events_kind.sql (output), and TimescaleDB refuses a concurrent index build on a hypertable. Do not follow that advice on a hypertable. TimescaleDB’s own option for building an index on one is WITH (timescaledb.transaction_per_chunk).

The four known issues listed for 0.8.0 still apply. We re-checked three of them with the 0.8.1 binary: ptah mcp --help still says six reading tools, ptah migrations data --help still says the command applies no gating, and ptah schema export --to markdown still shows an undeclared primary key as nullable. The code that draws the ptah assist approval prompt did not change between the two releases.

Where to read more

The release page has the archives, the checksums and the list of changes. The documentation links below go to the 0.8.1 version of each page.

The online mode states what online: require proves, and the lock timeout it requires.
Change a schema without downtime covers the lock queue and the recovery from a canceled concurrent index.
Run an Atlas project on ptah-compat is the page to start from with an existing Atlas directory.
Compatibility differences lists where ptah-compat deliberately behaves differently from Atlas.

Example files

← All articles