v0.4.0

The Spanner provider checks the rows before a change they break

Spanner checks a new constraint, a unique index, NOT NULL and a shorter
character varying against every row, but only when the statement runs. The
provider replaces such a rule with one statement that drops it and another
that adds it back. When the rows broke the new rule, the drop had committed
and the table was left without the rule, or with half of a change. This
release counts the rows first and refuses the run before anything is sent.
Its support level is still best effort, measured against pgadapter-emulator
v0.56.2; the managed service and GoogleSQL are not covered.

Which Ptah it needs. The provider is built with provider-sdk v0.39.0
(contract 25.41). It still
starts on a Ptah of provider contract 25.27 or a later minor of 25. The row
checks need no particular Ptah. The change stream record needs
contract 25.41, which Ptah v0.25.0 speaks.

What changed

• A CHECK or foreign key the rows break (#34). Before the first
  statement of a run, the provider counts the rows that break each CHECK and
  foreign key the run adds after its first statement. When a row breaks one,
  nothing runs:

  unit 2 of 2 adds CHECK "items_kind_check" to table "items", which 1 row breaks, so no unit ran: Spanner refuses to add a constraint a row breaks; update or delete the rows that break it before adding the constraint
  

  A CHECK counts the rows where NOT (expr) holds, so a null result passes,
  as Spanner passes it. A foreign key counts the rows whose key columns all
  hold a value and match no referenced row. A narrowed enum list is such a
  CHECK. v0.3.0 stopped PARTIAL with the old CHECK or key gone.

• A unique index, NOT NULL or a shorter length the rows break (#35).
  The same check counts the rows whose key another row holds for an index made
  unique, the nulls for a column made NOT NULL, and the values longer, in
  characters, than a shorter character varying or its array elements allow.
  Rows with a null key column do not count, because Spanner leaves them out of
  a unique index. A column added NOT NULL without a default holds a null in
  every row, so the run that adds it to a table with rows is refused, where
  v0.3.0 added the column and left it nullable.

• When the check steps aside. The counts read the rows as they are before
  the run, so a statement before the addition that may change them leaves the
  addition to Spanner: a row change, a rename or a statement the provider
  cannot read ends the check, and a CREATE TABLE, DROP TABLE or change of a
  column the rule reads leaves that rule to Spanner. A rule the first statement
  adds is left to Spanner too, since its refusal applies nothing.

• What the check costs. Each count is a query over the whole table; the
  one for a unique index also groups by the key, and Spanner then checks the
  rows again when it adds the rule. The checks were measured on
  pgadapter-emulator with small tables only. On a large table expect the run
  to take longer and read more, and keep the query time limit in mind.

• Migrations still stop after the drop. ptah migrations up sends one
  statement per request. A migration's DROP CONSTRAINT or DROP INDEX runs
  in a request of its own, so when the rows break the ADD CONSTRAINT or
  CREATE UNIQUE INDEX that follows, the drop has already run and the table is
  left without the rule. For a CHECK, add the new one under another name
  first, then drop the old one.

• Change streams recorded as not described (#36, contract
  25.41). The provider does not describe change streams, so an export's
  silence about them read as the absence of every stream. Every read now
  records the kind change_stream as not described, with a stream in the
  database or without one, and the HCL header of ptah schema inspect and
  ptah-compat schema inspect carries the line Ptah's built-in Spanner engine
  wrote:

  // ptah:not-described change_stream reason=unsupported provenance=derived-from-target
  

  ptah schema inspect --format json lists the record, and an exported HCL
  document applies back with nothing to change. A Ptah older than contract
  25.41 receives no record, as before.

Install it with ptah providers install spanner.

Changelog

• 28f9f4a Record change streams as not described on every read (#36)
• 58d10ce Refuse a constraint the rows break before the first statement runs (#34)
• babf2b0 Refuse a unique index, NOT NULL or length the rows break up front (#35)

v0.3.0

The Spanner provider on provider contracts 25.37 and 25.39

This release stops replacing, on every plan, most views whose query Spanner
rewrites, and refuses a drop that a change stream names before any statement
runs. Its support level is still best effort, measured against
pgadapter-emulator v0.56.2; the managed service and GoogleSQL are not
covered.

Which Ptah it needs. The provider is built with provider-sdk v0.37.0
(contract 25.39). It still starts on a Ptah of provider contract 25.27 or a
later minor of 25, and serves an older minor without the additions it does not
list. The view query decisions need contract 25.37 and the plan-time refusal
of a drop needs contract 25.39; Ptah v0.24.0 speaks both. With Ptah v0.23.0
the provider compares views as v0.2.0 did, and refuses a drop a change stream
names when the statements run.

What changed

• Views Spanner rewrites (#30, contract 25.37). Spanner stores its own
  spelling of a view's query: casts on literals, parentheses, IN as = ANY (ARRAY[...]), BETWEEN as two comparisons, a table alias without AS,
  every column of a SELECT * named and the columns of a UNION qualified.
  Ptah compared the texts, so such a view was replaced with CREATE OR REPLACE VIEW on every plan. The provider now reads both queries into one form,
  typed by the declared columns Ptah sends, and answers that they are one
  query when the forms match. It leaves every other pair to Ptah's own
  comparison, so no view that converged with v0.2.0 replans. Of 33 view shapes
  measured, 21 were replaced on every plan; with Ptah v0.24.0, 3 are: the
  CASE, cast and subquery shapes. Declare those in the spelling Spanner
  stores to stop the replacement.

• A drop a change stream names, refused before the first statement (#31).
  Spanner refuses DROP TABLE of a table a change stream names in its FOR
  clause, and DROP COLUMN of a column one names in its column list, only
  when the statement runs, so ptah schema apply stopped PARTIAL with the
  statements before the drop applied. Before the first statement of a run, the
  provider now reads information_schema.change_stream_tables and
  change_stream_columns. When a statement drops what a stream names, nothing
  runs, and the reason names the statement, the table or column and the
  stream:

  unit 2 of 2 drops table "t", which change stream "cs1" names, so no unit ran: Spanner refuses to drop a table a change stream names; drop the change stream, or alter it to stop tracking the table, before dropping the table
  

  A stream FOR ALL names nothing, and a stream FOR t names no column of
  t, so neither stops a drop. A run that drops or alters the stream before
  its drops goes on, and Spanner decides those drops. The check works with any
  Ptah and covers a migration written by hand. ptah migrations up runs each
  statement on its own, so the statements before the drop have run, and the
  revision row records the provider's reason instead of SQLSTATE P0001.

• The same drop, refused at plan time (#33, contract 25.39). A read reports
  the change streams that name each table or some of its columns
  (schema.removal-blockers), and the planner refuses the drop before a
  statement exists. ptah schema apply --dry-run and ptah migrations generate refuse it too, and generate writes no file:

  dropping table "t": change stream "cs1" names it, and Spanner refuses to drop a table a change stream names; drop the change stream, or alter it to stop tracking the table, before dropping the table
  

  A Ptah older than contract 25.39 plans the drop, and the check above refuses
  the run.

• A changed enum list (#32). A changed list of an enum column's values
  planned nothing. With Ptah v0.24.0 the plan drops and adds the column's
  CHECK, and the next plan is synced. The provider needed no change; this
  release adds the live test and the documentation. Spanner runs the drop and
  the add one at a time, so when a row breaks the new CHECK the table is
  left without it: check the rows before you narrow a CHECK, an enum's list
  included.

Ptah v0.24.0 also fixes two Spanner paths on its own side. ptah schema apply --lock-timeout and PTAH_LOCK_TIMEOUT bound the wait for the provider's
lock, where the flag was refused and the variable reported as ignored. The
down file of a migration that adds a column with its own CHECK or foreign
key drops the constraint before the column, which Spanner requires.

Install it with ptah providers install spanner.

Changelog

• aec594f Assert that a changed enum list replaces the column's CHECK (#32)
• 79cccb3 Decide view queries Spanner rewrites under contract 25.37 (#30)
• 6fb0224 Refuse at plan time a drop a change stream names (#33)
• 41eff26 Refuse drops a change stream names before the first statement runs (#31)

v0.2.0

The Spanner provider after Ptah's built-in Spanner engine

Ptah no longer builds in a Spanner engine (stokaro/ptah#5657). This release is
the one that serves Spanner without it: the provider now states what the
built-in engine stated on its behalf, adopts provider contracts 25.29 and 25.31,
and carries the host Spanner tests the deletion removed. Its support level is
still best effort, measured against pgadapter-emulator v0.56.2; the managed
service and GoogleSQL are not covered.

Which Ptah it needs. The provider is built with provider-sdk v0.29.0
(contract 25.31). It still starts on a Ptah of provider contract 25.27 or a
later minor of 25, and serves an older minor without the additions it does not
list. It is built for a Ptah without a built-in Spanner engine: a Ptah release
that includes stokaro/ptah#5657. Schemas, the SQL TTL
clause, filtered indexes and the reserved seed ledger need contract 25.29
(stokaro/ptah#5641); enum columns need contract 25.31 (stokaro/ptah#5697). With
Ptah v0.22.1 the provider works as v0.1.0 did, and that release still sends
some commands to its own Spanner engine; use the next Ptah release.

What changed

• Offline capability facts (#17). The engine states the capability facts
  Ptah plans it with when no database is connected, the 149 facts of the
  deleted built-in engine's preset, and states them for its release line 0.
  ptah sql lint --dialect spanner reports CAP001 for CREATE INDEX CONCURRENTLY again, and ptah db capabilities reports Spanner's facts at
  best effort, where a Ptah without the built-in engine read PGAdapter's
  PostgreSQL 14.1 banner as certified PostgreSQL 14.
• Schemas (#19, contract 25.29). A declared schema, in SQL, Go or HCL, is
  created with CREATE SCHEMA IF NOT EXISTS, an empty one too, and the down
  file of a generated migration drops it. A read lists the schemas, so a held
  schema is not created again and one the declaration leaves out is kept. A
  rehearsal on a dev database removes the schemas it created there.
• The SQL TTL clause (#19, contract 25.29). A SQL schema document
  declares a row deletion policy with TTL INTERVAL '30 days' ON created_at
  after the column list. It plans, applies and converges, and the SQL that
  ptah db read writes now replays.
• Filtered indexes (#19, contract 25.29). An index with a WHERE clause of
  NOT NULL tests, the only predicate Spanner takes, is created and converges;
  a changed clause rebuilds the index, and ptah db read writes the clause.
  Ptah's built-in engine rebuilt such an index on every plan. Any other clause
  is refused by name.
• The seed ledger (#19, contract 25.29). A read reports schema_seeds as
  reserved, so ptah schema apply no longer plans to drop it after ptah seed.
• Enum columns (#22, contract 25.31). The engine states that an enum
  column is TEXT under a CHECK that lists its values, as the built-in
  engine built it, so Ptah builds that column instead of refusing an enum
  type.
• An index comment is refused (#21) before a statement is sent, as a table
  or column comment is: Spanner stores none, and a plan reported synced while
  the declaration held one.
• Documentation (#18). The pages drop the advice for a Ptah with a built-in
  Spanner engine and the search_path=public pin, and say that --disable MF104 reaches SP101.

Tests

The host Spanner tests stokaro/ptah#5657 deleted are ported or covered here
(#21, #19 and #22), so each runs against the emulator through the shipped host
binary. The live suite drops each test's database when the
test ends (#20), so a run stays under the emulator's 100 databases.

Install it with ptah providers install spanner.

The Spanner provider on provider contract 25.33

This release adopts provider contract 25.33 where Spanner needs it: Ptah reads
a SQL schema document as Spanner runs it, and a render says what Spanner does
not keep of a declared column type. It also makes ptah schema render --dialect spanner work without a database. Its support level is still best
effort, measured against pgadapter-emulator v0.56.2; the managed service and
GoogleSQL are not covered.

Which Ptah it needs. The provider is built with provider-sdk v0.31.0
(contract 25.33). It still starts on a Ptah of provider contract 25.27 or a
later minor of 25, and serves an older minor without the additions it does not
list. Both additions below need a Ptah of contract 25.33 (stokaro/ptah#5753);
with an older Ptah the provider works as v0.2.0 did.

What changed

• DROP VIEW IF EXISTS of a table in a SQL schema document (#25, contract
  25.33). The engine states that Spanner runs the statement and ignores it
  when the name is a table's (source.sql-statement-facts), so Ptah reads the
  document as the server runs it. An older Ptah refuses the document with the schema declares t as another kind of object, as it does for PostgreSQL,
  which refuses the statement.
• Render omissions (#25, contract 25.33). A column declared integer or
  int, or an array of either, keeps that spelling in the statement, and
  Spanner stores a bigint: the column takes values outside the 32-bit range.
  The render now reports this as an omission (render.omissions), with the
  CHECK that keeps the range of a scalar column. Ptah's Go API returns the
  omission with the statements, and a caller that must not lose a declaration
  refuses the rendering; the CLI prints no render omission.
• ptah schema render --dialect spanner (#25). An offline render names a
  table of the default schema without its schema, and the provider refused
  every table (a Spanner table operation names a table by its schema and name and nothing else). It now plans such a table in public, and the command
  writes the schema's statements.

Measured and not adopted

• View query equivalence (schema.view-query-equivalence). Spanner
  rewrites a view's query when it stores it, so a view with a literal in a
  condition, a join, IN, BETWEEN, LIKE, CASE, LIMIT or a table alias
  is replaced on every plan, as Ptah's built-in Spanner engine replaced it.
  The contract would let the provider decide, but Ptah then compares no view
  query by text, and the request carries neither the columns a SELECT *
  stands for nor a way to hand a pair back to Ptah's comparison: a provider
  answer would replan SELECT * and UNION views that converge today. A test
  pins which views converge.
• Table grants (planning.table-grants). The provider plans no table
  rebuild, carries no grants, and the emulator refuses CREATE ROLE and
  GRANT (Statement is not supported).

Install it with ptah providers install spanner.

Spanner's text read by Ptah's PostgreSQL-family lexer, and its lexical rules

The provider reads Spanner's text with pgsyntax from
github.com/stokaro/ptah/sqlsyntax v0.1.0, the lexer Ptah reads the
PostgreSQL family with (stokaro/ptah#5586, #24). A dollar-quoted string, an
escape string, a Unicode escape string and a nested block comment are read as
Spanner reads them, so a migration, a seed file or a ptah sql lint input that
holds one no longer fails: ptah sql lint on such a file stopped at exit 2
with a dollar-quoted string is not part of the provider's SQL. A string
constant is one token, and a refusal names the construct that does not close
and its line.

The engine also states its lexical rules under query.lexical (provider
contract 25.34, stokaro/ptah#5754): escape strings, continued and Unicode
strings and nested comments, each measured on pgadapter-emulator v0.56.2. A
Ptah of contract 25.34 reads Spanner's SQL schema files by them, so
DEFAULT E'it\'s' and a nested comment no longer break a file; an older Ptah
receives the query dialect without the rules. The provider builds with
provider-sdk v0.32.0 and still starts on a Ptah of contract 25.27.

Changelog

• c7b580c Adopt contract 25.29: schemas, the SQL TTL clause, the seed ledger, filtered indexes (#19)
• 8c8de17 Adopt contract 25.33: SQL statement facts and render omissions (#25)
• 7666e83 Assert the order of a cyclic down file that Spanner requires (#23)
• 36613b4 Drop each live test's database when the test ends (#20)
• 81dd2c9 Drop the docs for a Ptah that builds in Spanner (#18)
• e285032 Gate a release on tests of the binary it packages (#27)
• 44e33ea Port the host's remaining Spanner tests to the provider (#21)
• 16e9ad8 Publish only a commit whose CI run passed (#28)
• 127e82d Read Spanner's text with the shared PostgreSQL-family lexer (#24)
• ec351d1 Run the conformance suite against the emulator (#29)
• f89a7e8 State that an enum column is TEXT under a CHECK (#22)
• 50d93bc State the capability facts Ptah plans Spanner with offline (#17)

v0.1.0

The first release of the Spanner provider

This provider serves Spanner's PostgreSQL interface, through PGAdapter, as a
separate executable for Ptah. It reads a live database, compares and plans,
renders and applies changes, keeps the migration history and the migration
lock, lints migrations and writes declared rows. Its support level is best
effort, measured against pgadapter-emulator v0.56.2; the managed service and
GoogleSQL are not covered.

Which Ptah it needs. The provider negotiates contract 25.27 or later, and
reads and plans foreign keys and CHECKs on contract 25.28. Three host changes
it relies on are on Ptah master and not in Ptah v0.22.1: constraint planning of
created tables (stokaro/ptah#5559), dev databases named by their session
(stokaro/ptah#5565) and the rollback prediction of foreign keys
(stokaro/ptah#5585). With v0.22.1, migrations generate of a schema with
foreign keys fails, and a Ptah that still builds in its own Spanner engine
sends some commands to that engine; use the next Ptah release.

Install it with ptah providers install spanner. The pages in docs/ describe
connecting, declarations, migrations and what the provider refuses; they are
published on docs.ptah.run under Extend, Providers, Spanner.

Changelog

• 51fda36 Add the scaffold of the Spanner provider (#1)
• 4111017 Carry views and sequences on the common-object wire (#10)
• 7c92548 Clean dev databases and guard replays on Spanner (#6)
• 5e810c5 Document the provider for docs.ptah.run (#15)
• c41323b Initial commit
• ab1241b Keep migration history, log and tags through the provider (#9)
• bc5d67b Own the lifecycle of Spanner tables (#4)
• 6881a2b Pin provider-sdk to the v0.1.0 tag (#2)
• 3b69f10 Read tables without a primary key and create a table's schema (#12)
• cdcad2c Read, compare and plan foreign keys and CHECKs on contract 25.28 (#13)
• 4ea1aaf Read, plan and write a table's row deletion policy (#7)
• 90e2782 Run the foreign-key migration up again after its down (#14)
• 5102397 Run the statements an operator writes through the provider (#11)
• 1c82d1c Serve migration lint for Spanner (#5)
• 55a56ab Serve tables through Spanner's PostgreSQL interface (#3)
• 0bbac61 Type, read and write declared rows and run seed files (#8)