What the Spanner provider refuses

Before you plan a Spanner change, check here whether the provider refuses the object or the change, whether it describes the object yet, and whether the emulator its tests run behaves like the managed service. A refusal names the object and the reason, and comes before any statement is sent unless a section here says otherwise. Ptah plans to create what a read leaves out, so an object the provider cannot describe is refused rather than skipped.

Support level

Support is best effort. The live tests run the Cloud Spanner emulator behind PGAdapter, gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator:v0.56.2, which is evidence about the PostgreSQL interface only. No test runs against a managed Spanner instance, and the provider serves no GoogleSQL database. Review the statements a plan writes before you apply them to a managed database.

Objects a read refuses

A read fails with a reason, rather than leave out an object it does not describe:

Object in the database	Reason the read gives
An interleaved table	table "public"."t" is interleaved in a parent table, which the provider does not describe yet
A generated column	column "c" of table "public"."t" is generated, which the provider does not describe yet
An identity column other than the hidden key of a table without a primary key	column "c" of table "public"."t" is an identity column, which the provider does not describe yet
A search index, a null-filtered index, or an index interleaved in a parent table	index "i" of table "public"."t" is a SEARCH index, which the provider does not describe yet, and the matching reason for the other two
An index with a WHERE clause, on a Ptah that does not compare index predicates (provider contract 25.28 or older)	index "i" of table "public"."t" has a WHERE filter, which needs a host that compares index predicates (schema.index-predicates)
An index whose filter the provider cannot read back as NOT NULL tests on the index’s columns	index "i" of table "public"."t" has a WHERE filter the provider cannot read: ...
A sequence that skips a range of values	sequence "public"."s" skips a range of values, which the provider does not describe yet
An index Spanner is still building, or a foreign key or CHECK it is still validating	... is still being built; read again once Spanner reports it ready, or still being validated for a constraint

The NOT NULL tests Spanner keeps on every key column of a unique index are not a user filter: a read leaves them out, and a unique index without a filter of its own reads as one without a WHERE clause. The index Spanner builds for a foreign key, and the CHECK it keeps for a NOT NULL column, are Spanner’s own and are left out of a read without a refusal.

A Ptah that does not take a table’s foreign keys and CHECK constraints (provider contract 25.27) gets the read of a table that holds one refused, and a Ptah that does not carry views and sequences gets the read of a schema that holds one refused, each naming the object and the contract feature it needs.

Declarations the provider refuses

Declaration	Where it is described
smallint, serial, timestamp, json, char, xml, interval, a type modifier and the other types Spanner does not have	Column types
A descending or prefixed key part, and a key with INCLUDE, a method or deferral	Primary keys
A collation, an engine, a comment, custom table SQL, a trigger, an identity or generated column, and uniqueness over an expression	What a table cannot carry
An index with a WHERE clause Spanner cannot create, an access method, an operator class, storage parameters, NULLS NOT DISTINCT, an expression key or a prefix	Indexes
ON UPDATE, an ON DELETE action other than NO ACTION or CASCADE, deferral, and a foreign key on jsonb, numeric, real or an array	Foreign keys and CHECK constraints
A row deletion policy with a unit, without whole days, or on a column that is not timestamptz	Row deletion policies
A materialized view, a view option, with_check and a view comment	Views
A sequence option other than its counter start	Sequences
A comment on an index	Indexes

Spanner’s PostgreSQL interface has no role, grant, row-level security policy, stored function or comment, and the provider carries none of them. Ptah refuses a declaration that writes one, naming it, such as the experimental provider contract does not support roles on the spanner engine, whose provider does not carry them.

Changes the provider refuses

These changes of a kept table are refused before a statement is sent, because Spanner refuses them:

• a change of a primary key’s columns or of their order;
• a type change of a key column;
• a type change other than between character varying of any length and bytea;
• the drop of the column a kept row deletion policy counts from.

Create a new table with the shape you need and copy the rows. A sequence change other than its counter start is refused too.

The drop of a table that a change stream names in its FOR clause, and of a column that one names in its column list, is refused too. Spanner refuses such a drop itself (Table t is tracked by 1 Change Stream: cs1), but only when it runs, after the statements before it. A read reports the change streams that name each table, from information_schema.change_stream_tables and change_stream_columns, and on a Ptah of provider contract 25.39 the plan is refused before a statement exists. ptah schema apply, --dry-run and ptah migrations generate all refuse it:

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 of contract 25.38 or older does not carry the streams to the plan, so it plans the drop, and the provider refuses it when the statements run. Before the first statement of a run, the provider reads the same catalog. When a statement of the run drops what a stream names, nothing of the run is sent, and the reason names the unit, 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

This check also covers a migration someone writes by hand. Drop the stream, or alter it with ALTER CHANGE STREAM, first. A run that drops or alters a change stream before its drops leaves those drops to Spanner, because what the streams name has changed by then. A stream created FOR ALL names nothing, and a stream FOR t names no column of t, so neither stops a drop. ptah migrations up runs each statement of a migration on its own, so the statements of a migration before the drop have run when the drop is refused.

Clauses Ptah does not hand the provider

On a Ptah of provider contract 25.28 or older, some declarations stop in Ptah before they reach the provider, because that contract has no place for them. A Ptah of contract 25.29 hands each of them to the provider:

• A SQL schema file’s row deletion clause, TTL INTERVAL '30 days' ON created_at, is refused with the experimental provider contract does not support Table.Overrides. Declare the policy in Go, YAML or HCL there. An export that ptah db read writes carries the clause, so the export does not replay through the provider on such a Ptah.
• A schema declared as an object of its own, through SQL CREATE SCHEMA, a Go //ptah:schema:schema directive or an HCL schema block other than public, is refused with the experimental provider contract does not support SchemaDiff.SchemasAdded, also when the schema exists. A table in such a schema, declared in Go, YAML or SQL without the schema object, is created after CREATE SCHEMA IF NOT EXISTS. An HCL table always names a schema block, so an HCL table outside public is refused.
• An index with a WHERE clause is refused, in a declaration and in a read.

Gaps without a refusal

These cases plan something Spanner refuses, or plan more than the declaration asks for, and nothing refuses them before the plan runs. The work that closes them is part of stokaro/ptah#4204.

• Change streams. A read does not describe change streams: it reports only which tables and columns they name, for the plan to refuse a drop (see Changes the provider refuses). An export writes no change stream and carries no line that marks change streams as not described.
• The seed ledger on an older Ptah. On a Ptah of provider contract 25.28 or older, after ptah seed has run, ptah schema apply plans DROP TABLE "schema_seeds" when the declaration does not name the table. Review the plan, or declare the table. A Ptah of contract 25.29 leaves the ledger out of every plan.
• A column added with a CHECK. The down file that ptah migrations generate writes for it drops the column first, and Spanner refuses that (Cannot drop column score on which there is a dependent check constraint). Edit the down file to drop the constraint before the column.
• WITH CHECK OPTION in a SQL schema file. The clause stays in the view’s query text, the plan writes it, and Spanner refuses the statement when it runs, after the statements before it. Leave the clause out: Spanner’s views cannot be written through.
• A CHECK the rows already break. A changed CHECK, an enum column’s list of values included, is replaced by DROP CONSTRAINT and then ADD CONSTRAINT. Spanner runs each statement on its own, so when a row breaks the new CHECK the drop has run and Spanner refuses the add: the table is left without the CHECK. Check the rows before you narrow a CHECK.
• A condition Spanner rewrites. A view or a CHECK whose text the provider cannot read in Spanner’s spelling is replaced on every plan, and so is any rewritten view under a host older than contract 25.37; see Views.

Where the emulator differs

The emulator behind PGAdapter 0.56.2 differs from the documented service in ways that change what a plan does:

• For a foreign key, Spanner documents key_column_usage.position_in_unique_constraint as the column’s position in the referenced key, and the provider reads it that way. The emulator reports each key column’s own position instead. A composite foreign key that lists the referenced columns in another order than the referenced key, such as FOREIGN KEY (y, x) REFERENCES parent (b, a) against PRIMARY KEY (a, b), therefore reads back paired in the key’s order, and every plan drops and adds it. The server still enforces the key as declared. A key that lists the referenced columns in the key’s order reads back as declared.
• The emulator runs one read-write transaction at a time, and a write beside another session’s open transaction waits until it aborts one of them. Reads do not wait. The migration lock is built around this.
• The emulator refuses CREATE ROLE, so the dev database cleanup’s refusal of roles and grants, and the replay guard’s, are untested on the managed service.
• The emulator holds at most 100 databases per instance.