RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/spanner/docs/commit-timestamp below:

Commit timestamps in GoogleSQL-dialect databases | Spanner

This topic describes how to write a commit timestamp for each insert and update operation that you perform with Spanner. To use this feature, set the allow_commit_timestamp option on a TIMESTAMP column, then write the timestamp as part of each transaction.

Overview

The commit timestamp, based on TrueTime technology, is the time when a transaction is committed in the database. The allow_commit_timestamp column option allows you to atomically store the commit timestamp into a column. Using the commit timestamps stored in tables, you can determine the exact ordering of mutations and build features like changelogs.

To insert commit timestamps in your database, complete the following steps:

Create a column with type TIMESTAMP with the column option allow_commit_timestamp set to true in the schema definition. For example:

CREATE TABLE Performances (
    ...
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
    ...
) PRIMARY KEY (...);

If you are performing inserts or updates with DML, use the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp.

If you are performing inserts or updates with mutations, use the placeholder string spanner.commit_timestamp() on insertions or updates to your commit timestamp column. You can also use the commit timestamp constant provided by the client library. For example, this constant in the Java client is Value.COMMIT_TIMESTAMP.

When Spanner commits the transaction using these placeholders as column values, the actual commit timestamp is written to the specified column (For example: the LastUpdateTime column). You could then use this column value to create a history of updates to the table.

Commit timestamp values are not guaranteed to be unique. Transactions that write to non-overlapping sets of fields might have the same timestamp. Transactions that write to overlapping sets of fields have unique timestamps.

Spanner commit timestamps have microsecond granularity, and they are converted to nanoseconds when stored in TIMESTAMP columns.

Create and delete a commit timestamp column

Use the allow_commit_timestamp column option to add and remove support for commit timestamps:

When creating a new table to specify that a column supports commit timestamps.
When altering an existing table:
- to add a new column supporting commit timestamps,
- to alter an existing TIMESTAMP column to support commit timestamps,
- to alter an existing TIMESTAMP column to remove commit timestamp support

Keys and indexes

You can use a commit timestamp column as a primary key column or as a non-key column. Primary keys can be defined as ASC or DESC.

ASC (default) - Ascending keys are ideal for answering queries from a specific time forward.
DESC - Descending keys keep the latest rows at the top of the table. They provide quick access to the latest records.

The allow_commit_timestamp option must be consistent across the primary keys of parent and child tables. If the option is not consistent across primary keys, Spanner returns an error. The only time the option can be inconsistent is when you are creating or updating the schema.

Using commit timestamps under the following scenarios creates hotspots which reduce data performance:

Commit timestamp column as the first part of the primary key of a table:

CREATE TABLE Users (
  LastAccess TIMESTAMP NOT NULL,
  UserId     INT64 NOT NULL,
  ...
) PRIMARY KEY (LastAccess, UserId);

The first part of the primary key of a secondary index:

CREATE INDEX UsersByLastAccess ON Users(LastAccess)

CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)

Hotspots reduce data performance, even with low write rates. There is no performance overhead if commit timestamps are enabled on non-key columns that are not indexed.

Create a commit timestamp column

The following DDL creates a table with a column that supports commit timestamps.

CREATE TABLE Performances (
    SingerId        INT64 NOT NULL,
    VenueId         INT64 NOT NULL,
    EventDate       Date,
    Revenue         INT64,
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE

Adding the option changes the timestamp column as follows:

You can use the spanner.commit_timestamp() placeholder string (or a constant provided by the client library) for inserts and updates.
The column can only contain values in the past. For more information, see Providing your own value for the timestamp.

The option allow_commit_timestamp is case sensitive.

Add a commit timestamp column to an existing table

To add a commit timestamp column to an existing table, use the ALTER TABLE statement. For example to add a LastUpdateTime column to the Performances table, use the following statement:

ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
    NOT NULL OPTIONS (allow_commit_timestamp=true)

Convert a timestamp column to a commit timestamp column

You can convert an existing timestamp column into a commit timestamp column, but doing so requires Spanner to validate that the existing timestamp values are in the past. For example:

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=true)

You cannot change the data type or NULL annotation of a column in an ALTER TABLE statement that includes SET OPTIONS. For details, see Data Definition Language.

Remove the commit timestamp option

If you want to remove commit timestamp support from a column, use the option allow_commit_timestamp=null in an ALTER TABLE statement. The commit timestamp behavior is removed, but the column is still a timestamp. Changing the option does not alter any other characteristics of the column, such as type or nullability (NOT NULL). For example:

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=null)

Write a commit timestamp using a DML statement

You use the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp in a DML statement. Spanner selects the commit timestamp when the transaction commits.

Note: After you call the PENDING_COMMIT_TIMESTAMP function, the table and any derived index is unreadable to any future SQL statements in the transaction. Because of this, the change stream can't extract the previous value for the column that has a pending commit timestamp, if the coloumn is modified again later in the same transaction. You must write commit timestamps as the last statement in a transaction to prevent the possibility of trying to read the table. If you try to read the table, then Spanner produces an error.

The following DML statement updates the LastUpdateTime column in the Performances table with the commit timestamp:

UPDATE Performances SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

The following code example uses the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp in the LastUpdateTime column.

C++ C# Go Java Node.js PHP Python Ruby Ruby

Commit timestamps can only be written to columns annotated with the allow_commit_timestamp=true option.

If you have mutations on rows in multiple tables, you must specify spanner.commit_timestamp() (or the client library constant) for the commit timestamp column in each table.

Query a commit timestamp column

The following example queries the commit timestamp column of the table.

C++ C# Go Java Node.js PHP Python Ruby Provide your own value for the commit timestamp column

You can provide your own value for the commit timestamp column, instead of passing spanner.commit_timestamp() (or client library constant) as the column value. The value must be a timestamp in the past. This restriction ensures that writing timestamps is an inexpensive and fast operation. The server returns a FailedPrecondition error if a future timestamp is specified.

Note: The CURRENT_TIMESTAMP value is not based on true time. So, the value it returns is not necessarily in the past and cannot be used to compare with a commit timestamp value. Create a changelog

Suppose that you want to create a changelog of every mutation that happens to a table and then use that changelog for auditing. An example would be a table that stores the history of changes to word processing documents. The commit timestamp makes creating the changelog easier, because the timestamps can enforce ordering of the changelog entries. You could build a changelog that stores the history of changes to a given document using a schema like the following example:

CREATE TABLE Documents (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Contents   STRING(MAX) NOT NULL,
) PRIMARY KEY (UserId, DocumentId);

CREATE TABLE DocumentHistory (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Ts         TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Delta      STRING(MAX),
) PRIMARY KEY (UserId, DocumentId, Ts),
  INTERLEAVE IN PARENT Documents ON DELETE NO ACTION;

To create a changelog, insert a new row in DocumentHistory in the same transaction in which you insert or update a row in Document. In the insertion of the new row in DocumentHistory, use the placeholder spanner.commit_timestamp() (or client library constant) to tell Spanner to write the commit timestamp into column Ts. Interleaving the DocumentsHistory table with the Documents table will allow for data locality and more efficient inserts and updates. However, it also adds the constraint that the parent and child rows must be deleted together. To keep the rows in DocumentHistory after rows in Documents are deleted, do not interleave the tables.

Optimize recent-data queries with commit timestamps

Commit timestamps enable a Spanner optimization that can reduce query I/O when retrieving data written after a particular time.

To activate this optimization, a query's WHERE clause must include a comparison between a table's commit timestamp column and a specific time that you provide, with the following attributes:

Provide the specific time as a constant expression: a literal, a parameter, or a function whose own arguments evaluate to constants.
Compare whether the commit timestamp is more recent than the given time, through either the > or >= operators.
Optionally, add further restrictions to the WHERE clause with AND. Extending the clause with OR disqualifies the query from this optimization.

For example, consider the following Performances table, which includes a commit timestamp column:

CREATE TABLE Performances (
    SingerId INT64 NOT NULL,
    VenueId INT64 NOT NULL,
    EventDate DATE,
    Revenue INT64,
    LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate);

This query benefits from the commit-timestamp optimization described earlier, because it has a greater-than-or-equal-to comparison between the table's commit timestamp column and a constant expression—in this case, a literal:

SELECT * FROM Performances WHERE LastUpdateTime >= "2022-05-01";

The following query also qualifies for the optimization, since it has a greater-than comparison between the commit timestamp and a function whose arguments all evaluate to constants during the query's execution:

SELECT * FROM Performances
  WHERE LastUpdateTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

What's next

Use commit timestamps to create a change log with Go.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4