Does Google Cloud Spanner support default column values?

Question

Having the capability to compute a UTC timestamp as the default value for a column is a handy feature of most popular database solutions. Does Google Cloud Spanner support this? If not, is this a possible roadmap item?

Tandem

Answer 1

Cloud Spanner doesn't actually allow any default value to be specified regardless of type. This means the implicit default is Null, or Error, depending on whether to column was specified with NOT NULL

Cloud Spanner internally stores a 'timestamp' of when a row was committed, but it doesn't expose this directly. It also doesn't behave like a default value (set once), so unfortunately the answer to your question is currently no.

Definitely something for the team to consider.

Sharma

Is there any way to access that internal committed timestamp? Almost every schema I've ever worked with adds created timestamps to the tables, but apart from that it would be useful to know the committed timestamps of rows because of how spanner's transactional isolation is based on timestamps and you can perform reads at a specified timestamp (not too far in the past.) – Suburbanize

Answer 2

There is the ability to get the commit timestamp in the Spanner table.

The basic steps are:

• Create a column with type TIMESTAMP with the column option allow_commit_timestamp set to true in the schema definition: my column TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)

• In your DML inserts or updates, use the PENDING_COMMIT_TIMESTAMP() function to write the commit timestamp.

• In mutations, use spanner.commit_timestamp()

As of April 2022 release notes, Cloud Spanner now supports default values for columns.

So this schema would work:

CREATE TABLE foo (
  fooUUID STRING(36) NOT NULL,
  created TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (fooUUID);

INSERT INTO foo (fooUUID) VALUES ('bar');

SELECT * FROM foo;
#result:
fooUUID     created
bar         2022-05-12T15:08:39.164778107Z

Default values don't work with commit timestamps from the first example.

These, and other limitations around timestamp columns, are provided here:

A column with a default value can't be a commit timestamp column. PENDING_COMMIT_TIMESTAMP() can't be used as a default value. SET OPTIONS (allow_commit_timestamp = true) is disallowed.

And if you are attempting to create a generated column based on other column values:

The expression can't contain non-deterministic functions such as PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), and CURRENT_TIMESTAMP().

Ornis