Repository navigation
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
CHECKor foreign key the rows break (#34). Before the first
statement of a run, the provider counts the rows that break eachCHECKand
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 constraintA
CHECKcounts the rows whereNOT (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 stoppedPARTIALwith the oldCHECKor key gone. -
A unique index,
NOT NULLor 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 madeNOT NULL, and the values longer, in
characters, than a shortercharacter varyingor its array elements allow.
Rows with a null key column do not count, because Spanner leaves them out of
a unique index. A column addedNOT NULLwithout 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 aCREATE TABLE,DROP TABLEor 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-emulatorwith 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 upsends one
statement per request. A migration'sDROP CONSTRAINTorDROP INDEXruns
in a request of its own, so when the rows break theADD CONSTRAINTor
CREATE UNIQUE INDEXthat follows, the drop has already run and the table is
left without the rule. For aCHECK, 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 kindchange_streamas not described, with a stream in the
database or without one, and the HCL header ofptah schema inspectand
ptah-compat schema inspectcarries the line Ptah's built-in Spanner engine
wrote:// ptah:not-described change_stream reason=unsupported provenance=derived-from-targetptah schema inspect --format jsonlists 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.