SQL Transactions

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

Transaction operations

INSERT

Inserts documents into a table.

• 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

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

• 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

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.

• 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

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

• 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

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.

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.

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

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

• 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

• 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.

• Additionally, for read-write transactions:

  • SYSTEM_TIME overrides the system-time of the transaction, used for an initial backfill of the database. It must not be earlier than any other transaction that has been submitted to the database. 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.
  • TIMEZONE sets the time zone for the duration of the transaction, affecting any time zone-aware date/time literals and functions. If not provided, it defaults to the time-zone of the connection.

• 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.