Skip to content

SQL Transactions

For examples on how to submit SQL transactions in each client library, see the individual driver documentation.

Transaction operations

Section titled “Transaction operations”

INSERT

Section titled “INSERT”

Inserts documents into a table.

INSERT INTO <table name> ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) VALUES ( <value> , ) , ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) RECORDS <object> <param> , ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) ( ( ( <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , <column name> <column name> <column name> , , , ) ) ) <select query>
  • Documents must contain an _id column.
  • By default, the document will be inserted for valid-time between now and end-of-time. This can be overridden by including _valid_from and/or _valid_to columns in the document.
  • If the document already exists, ‘insert’ behaves like an upsert - it will overwrite the existing document for the valid-time range specified (or now → end-of-time if not provided).

UPDATE

Section titled “UPDATE”

Updates documents in a table, optionally for a period of valid-time.

UPDATE <table name> FOR PORTION OF VALID_TIME FROM <timestamp> TO <timestamp> FOR ALL VALID_TIME FOR VALID_TIME ALL SET <column> = <value> , WHERE <predicate>
  • If the valid-time range is not provided, the effective valid-time range of the update will be from now to the end of time. (SQL:2011 specifies that updates without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)
  • The _id column cannot be updated - instead, users should delete this document and re-insert a new one.
  • The valid-time columns cannot be updated outside of the for-valid-time clause (i.e. not in the SET clause).

PATCH

Section titled “PATCH”

Patches documents already in a table with the given document - updating those that exist, inserting any that don’t (an ‘upsert’) - optionally for a period of valid-time.

PATCH INTO <table name> FOR PORTION OF VALID_TIME FROM <timestamp> TO <timestamp> RECORDS <object> <param> ,
  • If the valid-time range is not provided, the effective valid-time range of the update will be from now to the end of time.
  • The _id column cannot be patched - instead, users should delete this document and re-insert a new one.
  • The valid-time columns cannot be updated outside of the for-valid-time clause (i.e. not in the records themselves).
  • Documents are currently merged at the granularity of individual keys - e.g. if a key is present in the patch document, it will override the same key in the database document; if a key is absent or null, the key from the document already in the database will be preserved.

DELETE

Section titled “DELETE”

Deletes documents from a table, optionally for a period of valid-time.

DELETE FROM <table name> FOR PORTION OF VALID_TIME FROM <timestamp> TO <timestamp> FOR ALL VALID_TIME FOR VALID_TIME ALL WHERE <predicate>
  • If the valid-time clause is not provided, the effective valid-time range of the delete will be from now to the end of time. (SQL:2011 specifies that deletes without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)

ERASE

Section titled “ERASE”

Irrevocably erases documents from a table, for all valid-time, for all system-time.

While XTDB is immutable, in some cases it is legally necessary to irretrievably delete data (e.g. for a GDPR request). This operation removes data such that even queries as of a previous system-time no longer return the erased data.

ERASE FROM <table name> WHERE <predicate>

ASSERT

Section titled “ASSERT”

Rolls back the transaction if the provided predicate is false.

This is used to enforce constraints on the data in a concurrent environment, such as ensuring that a document with a given ID does not already exist.

ASSERT <predicate> <message>

If the optional message string is provided, it replaces the default error message text “Assert failed”, should the predicate fail.

  • We check to see whether the email address already exists in the database - if not, we can insert the new user.

    ASSERT NOT EXISTS (SELECT 1 FROM users WHERE email = 'james@example.com'), 'Email already exists!'
    

    INSERT INTO users (_id, name, email) VALUES ('james', 'James', 'james@example.com')

  • Check the xt.txs table for the transaction result to see if the assertion failed.

    SELECT * FROM xt.txs;
     _id  | committed |            error              |          system_time
    ------+-----------+-------------------------------+-------------------------------
        0 | t         | null                          | "2024-06-25T16:45:16.492255Z"
        1 | t         | null                          | "2024-06-25T16:45:26.985539Z"
        2 | f         | ... "Precondition failed" ... | "2024-06-25T16:45:32.577224Z"
    (3 rows)

COPY

Section titled “COPY”

Copies data directly into an XTDB table - usually significantly more efficiently than the equivalent `INSERT`s.

COPY <table name> FROM STDIN WITH ( FORMAT = 'transit-json' 'transit-msgpack' , )
  • A single COPY will atomically insert all of its documents within one transaction - if you’re using Kafka, we recommend you split your documents into batches of ~1-10k so as not to exceed Kafka’s message size limits.
  • If you’re using psql (or a similar tool) to connect to XTDB, those tools also support other sources in addition to STDIN - please see their own documentation for more details. For example, psql has a copy command which supports loading from a file.
  • Currently, there are two accepted formats: 'transit-json' and 'transit-msgpack'. For more details on the Transit format, see the available libraries for your language.
  • On the JVM, you can use Postgres’s CopyManager with an XTDB connection by calling conn.unwrap(PGConnection.class).getCopyAPI().
  • In the Clojure API, :put-docs uses COPY commands on your behalf.

BEGIN / COMMIT / ROLLBACK

Section titled “BEGIN / COMMIT / ROLLBACK”
BEGIN START TRANSACTION <transaction mode> , READ ONLY WITH ... READ WRITE WITH ( SYSTEM_TIME = = = = = = = = = = = = = = = = <timestamp> ASYNC = = = = = = = = = = = = = = = = <boolean> METADATA = = = = = = = = = = = = = = = = <value> TIMEZONE TIME ZONE = = = = = = = = = = = = = = = = <timezone> , ) COMMIT SYNC ASYNC ROLLBACK

  • A transaction may be either READ ONLY or READ WRITE. If not specified, it will be inferred from the first statement in the transaction.

    Transactions must not mix query statements and DML statements.

  • BEGIN’s transaction modes are those of SET TRANSACTION: BEGIN READ WRITE, SYSTEM_TIME = ... opens a transaction and applies SET TRANSACTION READ WRITE, SYSTEM_TIME = ... to it.

  • BEGIN READ WRITE WITH (...) takes the same options inside WITH (...), TIMEZONE included.

  • COMMIT (v2.2+) optionally takes SYNC or ASYNC, choosing whether the connection waits for the transaction to be indexed before returning. COMMIT SYNC waits for indexing; COMMIT ASYNC returns as soon as the transaction is submitted to the log. When given, this takes precedence over the transaction’s ASYNC option; otherwise that option applies, defaulting to SYNC.

  • N.B. READ WRITE is a misnomer in XTDB here - this is to align with standard SQL syntax. XTDB doesn’t have interactive read-write transactions, so any attempt to (e.g.) SELECT in this transaction will error.

  • For read-only transactions, see the query reference.

  • BEGIN, COMMIT and ROLLBACK are SQL statements over the Postgres wire protocol only - see the query reference.

SET TRANSACTION (v2.2+)

Section titled “SET TRANSACTION (v2.2+)”
SET TRANSACTION READ ONLY ... READ WRITE SYSTEM_TIME = = = = = = = = = = = = = = = = <timestamp> DEFAULT ASYNC = = = = = = = = = = = = = = = = <boolean> DEFAULT METADATA = = = = = = = = = = = = = = = = <value> DEFAULT TIMEZONE TIME ZONE = = = = = = = = = = = = = = = = <timezone> DEFAULT ISOLATION LEVEL <isolation level> ,
  • For how a list of transaction modes is read, how a later SET TRANSACTION merges into an earlier one, when it is accepted, TIMEZONE, and read-only transactions, see the query reference.
  • For read-write transactions:
    • SYSTEM_TIME overrides the system-time of the transaction, used for an initial backfill of the database. It must be strictly later than the system-time of the latest completed transaction — a backfill submitting several transactions gives each its own system-time rather than sharing one. A system-time that doesn’t advance aborts the transaction, and the abort is recorded in xt.txs. The aborted transaction still takes a system-time of its own, 1µs past the latest completed one, so a retry has to advance on that rather than on the instant it asked for. Otherwise, the system-time of the transaction will be defined by the log.
    • ASYNC affects whether the connection will wait for the transaction to be indexed before returning from COMMIT. If not provided, it defaults to false - i.e. the connection will wait for the transaction to be indexed before returning. This can be overridden per-commit with COMMIT SYNC / COMMIT ASYNC (see above).
    • METADATA (v2.1+) can provided to attach arbitrary metadata to the transaction. This is then added to the xt.txs table in the user_metadata column. For example, you might use this to attach upstream request IDs, correlation IDs, or other data lineage information.