[SOLVED] In Cloud Spanner, Is ALTER table statement with multiple constraints operation atomic in nature?

In Cloud Spanner, Is ALTER table statement with multiple constraints operation atomic in nature?

We are currently migrating our MySQL instances to Spanner. We have found a way to run ALTER TABLE statements with multiple operations(e.g drops and recreates constraints with some additional checks). Exact DDL:

ALTER TABLE our_table
        DROP CONSTRAINT chk_constraint_1
        ADD CONSTRAINT chk_constraint_1 CHECK(acted_type IN ('some', 'values', 'here'))
        ALTER COLUMN some_column INT64 NULL

Can we confirm if the above statement is atomic in nature and how does Spanner execute them under the hood?

Solution

Cloud Spanner only supports one operation per ALTER TABLE statement, meaning that you cannot combine both a DROP CONSTRAINT and ADD CONSTRAINT in the same statement. See https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#alter_table for the full definition of the ALTER TABLE statement.

You can however re-write the above example into three separate statements and send these as one batch to Cloud Spanner. If you are using one of client libraries of Cloud Spanner, then you do that by sending them as a list of statements to the UpdateDatabaseDdl method.

If you are using the JDBC driver, then you can use the START BATCH DDL and RUN BATCH statements to create a batch like this:

START BATCH DDL;
ALTER TABLE our_table DROP CONSTRAINT chk_constraint_1;
ALTER TABLE our_table
  ADD CONSTRAINT chk_constraint_1 CHECK(acted_type IN ('some', 'values', 'here'));
ALTER TABLE our_table ALTER COLUMN some_column INT64;
RUN BATCH;

You can also run the above statements in the web console Spanner Studio as one batch, by selecting all statements and clicking RUN SELECTED.

The above batch is not guaranteed to be atomic, and it could be that some statements execute successfully, while others fail. The batch is checked for typical errors, like syntax errors and misspelled table names, before being executed. This means that if you make a typo halfway the script, you would normally not end up with a script that is executed halfway. If however it turns out that adding the check constraint fails due to existing data, or if the statements become invalid due to other DDL statements being executed in parallel.

See https://cloud.google.com/spanner/docs/reference/rpc/google.spanner.admin.database.v1#updatedatabaseddlrequest for more details on how DDL batches work on Cloud Spanner (this information applies to both PostgreSQL and GoogleSQL-dialect databases).