Migrations on Spanner

ptah migrations up, down, status, set, repair, tag and log run against Spanner through this provider. Ptah’s apply reference describes the run for every engine; this page says what is particular to Spanner: Spanner commits every schema statement as it runs, so a migration is not one transaction, and the provider keeps the history and the lock in tables of its own.

Statements and transactions

Spanner refuses a schema statement inside an explicit transaction (DDL statements are only allowed outside explicit transactions). A migration file therefore cannot run as one transaction. The provider splits each file into its statements, and Ptah runs, counts and records them one at a time, in file order:

• A schema statement runs outside a transaction and commits as it runs.
• A statement that reads or changes rows (INSERT, UPDATE, DELETE, SELECT, WITH, VALUES, SHOW, or a query in parentheses) runs in a serializable transaction together with the revision row’s checkpoint, so the statement and the record of it commit together or not at all.

--tx-mode all is refused with tx-mode all is not supported for dialect "spanner": this target commits schema changes as they run, so a failed migration cannot be rolled back as a unit. The default mode, file, opens no transaction around a body on Spanner.

The provider refuses some files whole, before anything of them runs:

In a Spanner migration file	Why it is refused
BEGIN, START, COMMIT, END, ROLLBACK, ABORT, SAVEPOINT, RELEASE, PREPARE or RUN	The statement controls a transaction or a PGAdapter batch, and Ptah runs each statement of a Spanner migration on its own
SET or RESET	A session setting would not reach the statements after it, which may run on another connection
A dollar-quoted string, an unclosed quote or comment, or a character outside Spanner’s SQL	The provider cannot read the file into statements

Migration bodies the provider splits shows how such a refusal reads.

--statement-timeout and --lock-timeout, and the matching ptah.yaml keys, are refused before anything runs: Spanner has no statement or lock timeout that Ptah can set and restore around a migration. --migration-lock-timeout bounds the wait for the migration lock.

A migration that fails

A statement Spanner refuses applied nothing, and the statements before it stay applied. The revision row records that prefix, with the same partial checksum Ptah’s built-in Spanner engine recorded, so a database that engine left part way is resumed over the same digests. This file creates a table, writes a row into it, and then declares a type Spanner does not have:

CREATE TABLE a (id bigint PRIMARY KEY);
INSERT INTO a (id) VALUES (1);
CREATE TABLE b (id nosuchtype PRIMARY KEY);

ptah migrations status then reports the first two statements as applied, and table a holds its row. An excerpt of its output:

Dirty Migration: version=1 state=failed direction=up applied=2/3
Error: failed to execute migration SQL: provider execution failed (0 of 1 units confirmed): provider execution: NOT_APPLIED (0 units confirmed applied): Spanner refused unit 1 of 1 with SQLSTATE P0001
SQL: CREATE TABLE b (id nosuchtype PRIMARY KEY)
Error Statement: CREATE TABLE b (id nosuchtype PRIMARY KEY)

This migration stopped after 2 of 3 statements. Run 'ptah migrations repair --version 1 --resume-from 3' to run the rest, or repair with --force once you have run them yourself.

The error names the statement and the server’s SQLSTATE, not Spanner’s message: the provider never quotes driver text, because it can carry a value or a credential, and PGAdapter answers most Spanner refusals with P0001. Run the statement by hand to read Spanner’s own words. Fix the file, then resume with ptah migrations up --allow-dirty, which skips the statements the row records when they are unchanged, or follow the repair line the status prints.

A call that ends while Spanner is still applying a schema statement reports the statement’s outcome as unknown, because Spanner keeps applying a schema change its client stopped waiting for. Inspect the database before you repair the revision row.

A statement that drops a table, or a column, that a change stream names is refused before it runs, and the error names the table or column and the stream rather than a SQLSTATE. Drop or alter the stream in the migration, before the drop: the drop then runs, because each statement is checked when it runs.

A statement that adds a rule the rows must keep, such as a CHECK, a foreign key, a unique index, NOT NULL or a shorter character varying, is left to Spanner. The provider counts the rows that break a new rule only after the first statement of a run, and a migration runs one statement at a time. A migration that drops a CHECK or an index and adds one the rows break therefore stops after the drop, and the table is left without it. Add the new CHECK or unique index under another name before you drop the old one, or fix the rows first.

History tables

The provider keeps the migration records in the tables Ptah’s built-in Spanner engine kept them in, in that engine’s layouts, so a database that engine migrated keeps its history:

Table	Holds
schema_migrations	One revision row per migration, in Ptah’s format
atlas_schema_revisions	The revision rows with --revision-format atlas
schema_migrations_log	The migration log that ptah migrations log prints
ptah_migration_tags	The tags of ptah migrations tag, with a TIMESTAMPTZ column

The tables sit in the schema --migrations-schema names, or in the session’s default schema, public. The provider creates a missing table with the statement the built-in engine used, and refuses a table of one of these names in another layout rather than adopt it. A revision row of a rollback records its direction in its state, as pending:down or failed:down, because the native table has no direction column. The rows are read and written in Spanner’s read-write transactions, which change data only.

A read reserves the history tables, so ptah schema apply never plans their removal and an export never writes them.

The migration lock

Spanner has no session lock, so the provider keeps Ptah’s migration lock as a row of the table ptah_locks, which it creates on first use. ptah schema apply and the migration commands take the lock before they read the target. The row names its holder and the end of a 30-second lease:

• A holder checks the row once a second and renews the lease once less than 20 seconds of it are left, so it writes about once in ten seconds. The emulator runs one read-write transaction at a time, and a write beside another session’s open transaction waits until Spanner aborts one of them, so a write every second would stall the migration beside it.
• A holder that dies keeps the lock until its lease ends.
• A session that loses a lock it held runs nothing more.

A read reserves ptah_locks, so a plan never drops it and an export never writes it. Like every advisory lock, it keeps Ptah runs apart and does not stop a client that ignores it.

Dev databases

The provider empties a dev database through the cleanup Ptah runs before and after a rehearsal or a replay. It lists what the database holds and drops it in this order:

1. change streams;
2. views, in rounds, so a view goes before the views it reads;
3. foreign keys;
4. indexes;
5. tables, an interleaved table before its parent;
6. sequences and locality groups;
7. every schema but public, when the whole database is cleaned.

The lock table stays. The inventory is the database’s DDL as PGAdapter’s SHOW DATABASE DDL prints it, checked against information_schema. An object of a kind the provider has no statement for, such as a role, a grant or a property graph, stops the cleanup before it drops anything, and so does a table that a foreign key from outside the cleaned schemas references. The provider then removes the whole list or nothing. ptah db drop-all and ptah-compat schema clean empty a database through the same cleanup.

During a replay the provider judges each statement before Ptah runs it. It accepts what a cleanup can remove: creating, changing and dropping a table, an index, a view, a sequence, a change stream, a locality group or a schema, and reading and writing rows. It refuses a change to a database option, a role or a privilege, which outlive a cleanup, and a statement that sets session or transaction state, which would reach the statements after it.

Lint

ptah migrations lint --dialect spanner and ptah sql lint --dialect spanner read a file through the provider’s split, which takes every statement a migration holds, and Ptah’s rules for every engine run over it. The provider adds its own rules:

Rule	Severity	Finding
SP101	warning	SET NOT NULL validates every existing row, and a row holding NULL fails the schema change and leaves the column nullable
SP102	info	ALTER TABLE ... DROP TTL drops the row deletion policy and no column; it replaces the DS102 and BC104 findings, which read the statement as a drop of a column named TTL

For a migration that holds ALTER TABLE events ALTER COLUMN note SET NOT NULL; and ALTER TABLE events DROP TTL;, the two findings read:

lint/2_tighten.up.sql:1 [warning] SP101: SET NOT NULL runs as a schema change that validates every existing row, and a row holding NULL fails it (FAILED_PRECONDITION: Cannot specify a null value for column) and leaves the column nullable; backfill first (not-null validation scans existing rows)
lint/2_tighten.up.sql:2 [info] SP102: DROP TTL removes the table's row deletion policy and drops no column; rows the policy would have deleted are kept from now on (row deletion policy dropped)

Select the rule as SP101. Atlas’s MF104 in --disable reaches SP101, as it reaches PostgreSQL’s PG303. A directive names the rule as SP101: -- atlas:nolint MF104 silences neither SP101 nor PG303. --disable PG303, the code PostgreSQL’s SET NOT NULL check carries, is refused on a Spanner run with rule selector "PG303" does not match any registered rule.

ptah sql lint learns from the provider what each statement is: a table created without a primary key (DDL001), a statement it does not lint, such as an INSERT (SQL002), and CREATE INDEX CONCURRENTLY, which Spanner refuses (CAP001). A text the provider cannot read, such as one with a dollar-quoted string, fails the run with the reason.

Seeds and check assertions

ptah seed runs each seed file and its entry in the ledger public.schema_seeds in one read-write transaction. A read reports the ledger as reserved on a Ptah of provider contract 25.29, so ptah schema apply never plans to drop it. The ledger is the table and layout Ptah’s built-in Spanner engine kept, with checksum as TEXT and applied_at as TIMESTAMPTZ. A file that hits an existing key keeps nothing, and --idempotent records the file instead. A file that holds a schema statement, a transaction statement or a setting is refused before anything of it runs, naming the line. A session with an open transaction runs no seed, because the seed’s transaction cannot join it.

A migration’s -- +ptah check assertion runs through the provider. It runs only when it is one query that changes no row, and then in a read-only transaction. Its columns and rows come back in the forms declared rows use, and a type with no such form, such as an array, comes back as its text.