JDBC session management commands (PostgreSQL)

The Spanner JDBC driver (Java Database Connectivity) supports session management statements, which let you modify the state of your connection, execute transactions, and efficiently execute batches of statements.

The following commands apply to PostgreSQL-dialect databases.

Connection statements

The following statements make changes to or display properties of the current connection.

SHOW [VARIABLE] SPANNER.READONLY

Returns a result set with one row and one column of type BOOL indicating whether or not the connection is currently in read-only mode.

SET SPANNER.READONLY

Syntax
SET SPANNER.READONLY {TO|=} { true | false }

Sets whether the connection is in read-only mode.

You can execute this statement only while there is no active transaction.

SHOW [VARIABLE] AUTOCOMMIT

Returns a result set with one row and one column of type BOOL indicating whether the connection is in AUTOCOMMIT mode.

SET AUTOCOMMIT

Syntax
SET AUTOCOMMIT {TO|=} { true | false }

Sets the AUTOCOMMIT mode of the connection. The default is true.

You can execute this statement only when there is no active transaction.

When AUTOCOMMIT is false, a new transaction is initiated automatically after you execute COMMIT or ROLLBACK. The first statement that you execute starts the transaction.

SHOW [VARIABLE] SPANNER.RETRY_ABORTS_INTERNALLY

Returns a result set with one row and one column of type BOOL indicating whether the connection automatically retries aborted transactions.

SET SPANNER.RETRY_ABORTS_INTERNALLY

Syntax
SET SPANNER.RETRY_ABORTS_INTERNALLY {TO|=} { true | false }

Sets whether the connection automatically retries aborted transactions.

You can execute this command only after a transaction is started (see BEGIN [TRANSACTION | WORK]) and before any statements are executed within the transaction.

When you enable SPANNER.RETRY_ABORTS_INTERNALLY, the connection keeps a cryptographic checksum of all data that the connection returns to the client application and all of the update counts that the connection reports during the transaction. If Spanner aborts the transaction, the connection tries to execute the same transaction and checks if the returned data is exactly equal to data returned in the original transaction. If the data matches, Spanner continues the transaction. If the data does not match, the transaction fails by throwing AbortedDueToConcurrentModification.

This setting is enabled by default. We recommend turning off this setting if your application already retries aborted transactions.

SHOW [VARIABLE] SPANNER.AUTOCOMMIT_DML_MODE

Returns a result set with one row and one column of type STRING indicating the autocommit mode for Data Manipulation Language (DML) statements.

This variable has an effect only when you enable AUTOCOMMIT mode on the connection.

SET SPANNER.AUTOCOMMIT_DML_MODE

Syntax
SET SPANNER.AUTOCOMMIT_DML_MODE {TO|=} { 'TRANSACTIONAL' | 'PARTITIONED_NON_ATOMIC' }

Sets the autocommit mode for DML statements:

  • In TRANSACTIONAL mode, the driver executes DML statements as separate atomic transactions. The driver creates a new transaction, executes the DML statement, and either commits the transaction upon successful execution or rolls back the transaction in the case of an error.
  • In PARTITIONED_NON_ATOMIC mode, the driver executes DML statements as partitioned update statements. A partitioned update statement can run as a series of many transactions, each covering a subset of the rows impacted. The partitioned statement provides weakened semantics in exchange for better scalability and performance.

You can execute this statement only if you have enabled AUTOCOMMIT mode.

SHOW [VARIABLE] STATEMENT_TIMEOUT

Returns a result set with one row and one column of type STRING indicating the current timeout value for statements. The value is a whole number followed by a suffix indicating the time unit. A value of DEFAULT indicates that there is no timeout value set. If a statement timeout value has been set, statements that take longer than the specified timeout value will cause a java.sql.SQLTimeoutException and invalidate the transaction.

SET STATEMENT_TIMEOUT

Syntax
SET STATEMENT_TIMEOUT {TO|=} { '<int8>{ s | ms | us | ns }' | <int8> | DEFAULT }

Sets the statement timeout value for all subsequent statements on the connection.

The supported time units are:

  • s: seconds
  • ms: milliseconds
  • us: microseconds
  • ns: nanoseconds

The DEFAULT is 0 seconds, which means no timeout. An int8 number without units indicates ms. For example, the following commands set the statement timeout to 2 seconds.

SET STATEMENT_TIMEOUT TO 2000;
SET STATEMENT_TIMEOUT TO '2s';

A statement timeout during a transaction invalidates the transaction, all subsequent statements in the invalidated transaction (except ROLLBACK) fail, and the Spanner JDBC driver throws a java.sql.SQLTimeoutException.

SHOW [VARIABLE] SPANNER.READ_ONLY_STALENESS

Returns a result set with one row and one column of type STRING indicating the current read-only staleness setting that Spanner uses for read-only transactions and queries in AUTOCOMMIT mode. The default setting is STRONG.

SET SPANNER.READ_ONLY_STALENESS

Syntax
SET SPANNER.READ_ONLY_STALENESS {TO|=} staleness_type

staleness_type:

{ 'STRONG' 
  | 'MIN_READ_TIMESTAMP timestamp'
  | 'READ_TIMESTAMP timestamp'
  | 'MAX_STALENESS <int8>{ s | ms | us | ns }'
  | 'EXACT_STALENESS <int8>{ s | ms | us | ns }' }

Sets the read-only staleness setting for all subsequent read-only transactions while not in AUTOCOMMIT mode, and for all queries while in AUTOCOMMIT mode.

The timestamp bound options are as follows:

  • STRONG tells Spanner to perform a strong read.
  • MAX_STALENESS defines the time interval Spanner uses to perform a bounded staleness read, relative to now().
  • MIN_READ_TIMESTAMP defines an absolute time Spanner uses to perform a bounded staleness read.
  • EXACT_STALENESS defines the time interval Spanner uses to perform an exact staleness read, relative to now().
  • READ_TIMESTAMP defines an absolute time Spanner uses to perform an exact staleness read.

Timestamps must use the following format:

YYYY-[M]M-[D]D [[H]H:[M]M:[S]S[.DDDDDD]][timezone]

The supported time units for setting MAX_STALENESS and EXACT_STALENESS values are:

  • s: seconds
  • ms: milliseconds
  • us: microseconds
  • ns: nanoseconds

You can execute this statement only while there is no active transaction.

SHOW [VARIABLE] SPANNER.OPTIMIZER_VERSION

Returns a result set with one row and one column of type STRING indicating the optimizer version. The returned version is either numeric or 'LATEST'.

SET SPANNER.OPTIMIZER_VERSION

Syntax
SET SPANNER.OPTIMIZER_VERSION {TO|=} '<version>'|'LATEST'|''

Sets the version of the optimizer to be used for all the following statements on the connection. Setting the optimizer version to '' (the empty string) indicates to use the latest version. If no optimizer version is set, Spanner uses the optimizer version that is set at the database level.

SHOW [VARIABLE] SPANNER.OPTIMIZER_STATISTICS_PACKAGE

Returns a result set with one row and one column of type STRING indicating the current statistics package.

SET SPANNER.OPTIMIZER_STATISTICS_PACKAGE

Syntax
SET SPANNER.OPTIMIZER_STATISTICS_PACKAGE {TO|=} '<package>'|''

Sets the optimizer statistics package to use for all following statements on the connection. <package> must be a valid package name. Setting the package to '' (the empty string) indicates to use the latest package. If no optimizer statistics package is set, Spanner uses the optimizer statistics package that is set at the database level.

SHOW [VARIABLE] SPANNER.RETURN_COMMIT_STATS

Returns a result set with one row and one column of type BOOL indicating whether statistics should be returned for transactions on this connection. You can see returned statistics by using the SHOW [VARIABLE] COMMIT_RESPONSE command.

SET SPANNER.RETURN_COMMIT_STATS

Syntax
SET SPANNER.RETURN_COMMIT_STATS {TO|=} { true | false }

Indicates whether statistics should be returned for transactions on this connection. You can see returned statistics by using the SHOW [VARIABLE] COMMIT_RESPONSE command.

SHOW [VARIABLE] SPANNER.RPC_PRIORITY

Returns a result set with one row and one column of type STRING indicating the relative priority for Spanner requests. The priority acts as a hint to the Spanner scheduler and does not guarantee order of execution.

SET SPANNER.RPC_PRIORITY

Syntax
SET SPANNER.RPC_PRIORITY {TO|=} {'HIGH'|'MEDIUM'|'LOW'|'NULL'}

Sets the relative priority for Spanner requests executed by this connection. The priority acts as a hint to the Spanner scheduler and does not guarantee order of execution.

Transaction statements

The following statements manage and commit Spanner transactions.

SHOW [ VARIABLE ] TRANSACTION ISOLATION LEVEL

Returns a result set with one row and one column of type STRING. Returned value is always serializable.

SHOW [VARIABLE] SPANNER.READ_TIMESTAMP

Returns a result set with one row and one column of type TIMESTAMP containing the read timestamp of the most recent read-only transaction. This statement returns a timestamp only when either a read-only transaction is still active and has executed at least one query, or immediately after a read-only transaction is committed and before a new transaction starts. Otherwise, the result is NULL.

SHOW [VARIABLE] SPANNER.COMMIT_TIMESTAMP

Returns a result set with one row and one column of type TIMESTAMP containing the commit timestamp of the last read-write transaction that Spanner committed. This statement returns a timestamp only when you execute it after you commit a read-write transaction and before you execute any subsequent SELECT, DML, or schema change statements. Otherwise, the result is NULL.

SHOW [VARIABLE] SPANNER.COMMIT_RESPONSE

Returns a result set with one row and two columns:

  • COMMIT_TIMESTAMP (type=TIMESTAMP) Indicates when the most recent transaction was committed.
  • MUTATION_COUNT (type=int8) Indicates how many mutations were applied in the committed transaction.

This information is available only if SET RETURN_COMMIT_STATS was set to true prior to the transaction commit.

{ START | BEGIN } [ TRANSACTION | WORK ]

Syntax
{ START | BEGIN } [ TRANSACTION | WORK ] [{ READ ONLY | READ WRITE }]

Starts a new transaction. The keywords TRANSACTION and WORK are optional, equivalent, and have no effect.

  • Use COMMIT or ROLLBACK to terminate a transaction.
  • If you have enabled AUTOCOMMIT mode, this statement temporarily takes the connection out of AUTOCOMMIT mode. The connection returns to AUTOCOMMIT mode when the transaction ends.
  • If READ ONLY or READ WRITE is not specified, the transaction mode is determined by the default transaction mode of the session. This default is set by using the SET SESSION CHARACTERISTICS AS TRANSACTION command.

You can execute this statement only while there is no active transaction.

COMMIT [TRANSACTION | WORK]

Commits the current transaction. The keywords TRANSACTION and WORK are optional and equivalent, and have no effect.

  • Committing a read-write transaction makes all updates of this transaction visible to other transactions and releases all of the transaction's locks on Spanner.
  • Committing a read-only transaction ends the current read-only transaction. Any subsequent statement starts a new transaction. There is no semantic difference between COMMIT and ROLLBACK for a read-only transaction.

You can execute this statement only while there is an active transaction.

ROLLBACK [TRANSACTION | WORK]

Performs a ROLLBACK of the current transaction. The keywords TRANSACTION and WORK are optional and equivalent, and have no effect.

  • Performing a ROLLBACK of a read-write transaction clears any buffered mutations, rolls back the transaction on Spanner, and releases any locks the transaction held.
  • Performing a ROLLBACK of a read-only transaction ends the current read-only transaction. Any subsequent statements start a new transaction. There is no semantic difference between COMMIT and ROLLBACK for a read-only transaction on a connection.

You can execute this statement only while there is an active transaction.

SET TRANSACTION

Syntax
SET TRANSACTION { READ ONLY | READ WRITE }

Sets the transaction mode for the current transaction.

You can execute this statement only when AUTOCOMMIT is false, or if you have started a transaction by executing BEGIN [TRANSACTION | WORK] and have not yet executed any statements in the transaction.

This statement sets the transaction mode for the current transaction only. When the transaction commits or rolls back, the next transaction uses the default mode for the connection. (See SET SESSION CHARACTERISTICS.)

SET SESSION CHARACTERISTICS

Syntax
SET SESSION CHARACTERISTICS AS TRANSACTION { READ ONLY | READ WRITE }

Sets the default transaction mode for transactions in the session to READ ONLY or READ WRITE. This statement is permitted only when there is no active transaction.

The SET TRANSACTION command can override this setting.

SHOW [ VARIABLE ] SPANNER.STATEMENT_TAG

Returns a result set with one row and one column of type STRING that contains the request tag for the next statement. The request tag must have been previously set by the SET SPANNER.STATEMENT_TAG statement.

For more information, see Troubleshooting with request tags and transaction tags.

SET SPANNER.STATEMENT_TAG

Syntax
SET SPANNER.STATEMENT_TAG {TO|=} '<tag>'

Sets the request tag for the next statement to be executed. Only one tag can be set per statement. The tag doesn't span multiple statements; it must be set on a per statement basis. A request tag can be removed by setting it to the empty string ('').

For more information, see Troubleshooting with request tags and transaction tags.

SHOW [ VARIABLE ] SPANNER.TRANSACTION_TAG

Returns a result set with one row and one column of type STRING that contains the transaction tag for the next statement. The transaction tag must have been previously set by the SET SPANNER.TRANSACTION_TAG statement.

For more information, see Troubleshooting with request tags and transaction tags.

SET SPANNER.TRANSACTION_TAG

Syntax
SET SPANNER.TRANSACTION_TAG {TO|=} '<tag>'

Sets the transaction tag for the next transaction to be executed. Only one tag can be set per transaction. The tag doesn't span multiple transactions; it must be set on a per transaction basis. A transaction tag can be removed by setting it to the empty string ('').

For more information, see Troubleshooting with request tags and transaction tags.

Batch statements

The following statements manage batches of DDL statements and send those batches to Spanner.

START BATCH DDL

Starts a batch of DDL statements on the connection. All subsequent statements during the batch must be DDL statements. The DDL statements are buffered locally and sent to Spanner as one batch when you execute RUN BATCH. Executing multiple DDL statements as one batch is typically faster than running the statements separately.

You can execute this statement only while there is no active transaction.

RUN BATCH

Sends all buffered DDL statements in the current DDL batch to the database, waits for Spanner to execute these statements, and ends the current DDL batch.

If Spanner cannot execute at least one DDL statement, RUN BATCH returns an error for the first DDL statement that Spanner cannot execute. Otherwise, RUN BATCH returns successfully.

ABORT BATCH

Clears all buffered DDL statements in the current DDL batch and ends the batch.

You can execute this statement only when a DDL batch is active. You can use ABORT BATCH regardless of whether or not the batch has buffered DDL statements.

START BATCH DML

The following statements batch the two DML statements together and send these in one call to the server. A DML batch can be executed as part of a transaction or in autocommit mode.

START BATCH DML
INSERT INTO MYTABLE (ID, NAME) VALUES (1, 'ONE')
INSERT INTO MYTABLE (ID, NAME) VALUES (2, 'TWO')
RUN BATCH

Data Boost and partitioned query statements

The PartitionQuery API divides a query into smaller pieces, or partitions, and uses multiple machines to fetch the partitions in parallel. Each partition is identified by a partition token. The PartitionQuery API has higher latency than the standard query API, because it is only intended for bulk operations such as exporting or scanning the whole database.

Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned Spanner instance. Data Boost is only supported for partitioned queries.

You can enable Data Boost on the current connection with the SET SPANNER.DATA_BOOST_ENABLED statement.

The JDBC driver supports three alternatives for executing partitioned queries:

  • SET SPANNER.AUTO_PARTITION_MODE = true
  • RUN PARTITIONED QUERY sql
  • PARTITION sql followed by multiple RUN PARTITION 'partition-token'

Each of these methods are described in the following sections.

SHOW [VARIABLE] SPANNER.DATA_BOOST_ENABLED

SHOW SPANNER.DATA_BOOST_ENABLED

Returns a result set with one row and one column of type BOOL that indicates whether the connection uses Data Boost for partitioned queries.

SET SPANNER.DATA_BOOST_ENABLED

SET SPANNER.DATA_BOOST_ENABLED {TO|=} { true | false }

Sets whether this connection should use Data Boost for partitioned queries.

For a full example, see PostgreSQL DataBoostExample.

SHOW [VARIABLE] SPANNER.AUTO_PARTITION_MODE

SHOW SPANNER.AUTO_PARTITION_MODE

Returns a result set with one row and one column of type BOOL that indicates whether the connection automatically uses partitioned queries for all queries that are executed.

SET SPANNER.AUTO_PARTITION_MODE

SET SPANNER.AUTO_PARTITION_MODE {TO|=} { true | false}

Sets whether the connection should execute all queries as a partitioned query.

  • Set this variable to true if you want the JDBC connection to use partitioned query for all queries that are executed.
  • Also set SPANNER.DATA_BOOST_ENABLED to true if you want the JDBC connection to use Data Boost for all queries.
Example

This example executes two queries using Data Boost:

SET SPANNER.AUTO_PARTITION_MODE = true
SET SPANNER.DATA_BOOST_ENABLED = true
SELECT FirstName, LastName FROM Singers
SELECT SingerId, Title FROM Albums

For a full example, see PostgreSQL AutoPartitionModeExample.

RUN PARTITIONED QUERY

RUN PARTITIONED QUERY SELECT FirstName, LastName FROM Singers

Executes a query as a partitioned query on Spanner. Ensure that SPANNER.DATA_BOOST_ENABLED is set to true to execute the query with Data Boost:

SET SPANNER.DATA_BOOST_ENABLED = true
RUN PARTITIONED QUERY SELECT FirstName, LastName FROM Singers

The JDBC driver internally partitions the query and executes partitions in parallel. The results are merged into one result set and returned to the application. The number of worker threads executing partitions can be set with the variable SPANNER.MAX_PARTITIONED_PARALLELISM.

For a full example, see PostgreSQL RunPartitionedQueryExample.

PARTITION <SQL>

PARTITION SELECT FirstName, LastName FROM Singers

Creates a list of partitions to execute a query against Spanner and returns these a list of partition tokens. Each partition token can be executed on a separate JDBC connection on the same or another host using the RUN PARTITION 'partition-token' command.

RUN PARTITION 'partition-token'

RUN PARTITION 'partition-token'

Executes a query partition that has previously been returned by the PARTITION command. The command can be executed on any JDBC connection that is connected to the same database as the database that created the partition tokens.

For a full example, see PostgreSQL PartitionQueryExample.

SHOW [VARIABLE] SPANNER.MAX_PARTITIONED_PARALLELISM

SHOW SPANNER.MAX_PARTITIONED_PARALLELISM

Returns a result set with one row and one column of type INT64 that indicates the number of worker threads the JDBC connection uses to execute partitions. This value is used for:

  • SPANNER.AUTO_PARTITION_MODE = true
  • RUN PARTITIONED QUERY sql

SET SPANNER.MAX_PARTITIONED_PARALLELISM

SET SPANNER.MAX_PARTITIONED_PARALLELISM {TO|=} <bigint>

Sets the maximum number of worker threads that the JDBC driver can use to execute partitions.

Savepoint commands

The following statements enable and disable emulated savepoints in transactions. You can create a savepoint by calling the java.sql.Connection#setSavepoint() method.

The Spanner JDBC driver emulates savepoints to support frameworks that rely on these for nested transactions. Savepoints are emulated by keeping track of a running checksum for the results that have been returned by statements in the transaction. When rolling back to a savepoint, the Spanner JDBC driver rolls back the transaction, and then retries the transaction up to the point where the savepoint was set. The checksum of the retry is compared to the checksum of the initial transaction to verify that the same results were returned.

SHOW [VARIABLE] SPANNER.SAVEPOINT_SUPPORT

SHOW SPANNER.SAVEPOINT_SUPPORT

Returns a result set with one row and one column of type STRING indicating the current SAVEPOINT_SUPPORT configuration. Possible values are:

  • DISABLED: All savepoint commands are disabled and will fail.
  • FAIL_AFTER_ROLLBACK: Savepoint commands are enabled. Rolling back to a savepoint rolls back the entire transaction. The transaction fails if you try to use the transaction after rolling back to a savepoint.
  • ENABLED: All savepoint commands are enabled. Rolling back to a savepoint will rollback the transaction and retry is performed to the savepoint. This operation fails with an AbortedDueToConcurrentModificationException error if the underlying data that has been used by the transaction up to the savepoint has changed.

The default value is FAIL_AFTER_ROLLBACK.

SET SPANNER.SAVEPOINT_SUPPORT

SET SPANNER.SAVEPOINT_SUPPORT = { 'DISABLED' | 'FAIL_AFTER_ROLLBACK' | 'ENABLED' }

Sets the SAVEPOINT_SUPPORT mode of the connection.

You can execute this statement only while there is no active transaction.

What's next

Learn how to connect JDBC to a PostgreSQL-dialect database.