Transactions and parameters

Sessions

Transactions, prepared statements, settings and temporary objects belong to a session:

One statement runs in a session at a time. See Connect to VegaDB.

Transaction behavior

A statement outside a transaction commits on its own. A multi-statement transaction groups reads and writes so that they commit or roll back together:

BEGIN;
UPDATE sales.balances SET value = value + 5 WHERE id = 1;
DELETE FROM sales.balances WHERE id = 2;
INSERT INTO sales.balances VALUES (3, 30);
COMMIT;

• Snapshot. A transaction reads one snapshot of the catalog and sees its own uncommitted writes. Other sessions see those writes only after COMMIT.
• Conflicts. When a concurrent transaction committed a conflicting change to the same rows first, COMMIT fails with a write conflict and nothing is applied. Retry the transaction.
• Scope. A transaction can write several tables in one catalog. It cannot write to two catalogs; external read-only catalogs can still be read inside it.
• Errors. An error found before a statement runs, such as a syntax, binding or unknown-object error, leaves the transaction open. An error raised while a statement runs, such as a constraint violation, aborts the transaction: later statements fail until ROLLBACK, and COMMIT ends the transaction with an error reporting that its writes were discarded.
• Session loss. If a session ends while a transaction is open, the transaction rolls back. Committed transactions stay committed. A write whose commit response was lost is resolved with its request ID (HTTP retries).

Transactions by interface

Prepared parameters

Prepared statements take positional parameters: ? in order, or numbered $1, $2, … when a value is used more than once. A parameter's type comes from the expression around it or from an explicit cast.

SELECT order_id, net_amount
FROM sales.orders
WHERE customer_id = $1
  AND ordered_at >= $2::TIMESTAMPTZ;

Bind values through the driver API, never by interpolating strings into SQL. Flight SQL drivers send parameter values as Arrow data, so a parameter's Arrow type, including nested LIST, STRUCT and MAP types, reaches VegaDB unchanged. Binding several rows of parameters runs the statement once per row, all against one snapshot.

Typing parameters

Cast a parameter when nothing around it determines its type, such as a lone NULL or a value in the select list:

SELECT $1::UUID AS optional_customer_id;
SELECT $1::MAP(VARCHAR, VARCHAR) AS labels;

The HTTP SQL API takes complete SQL text. Use Flight SQL prepared statements when a client needs bound parameters.

Result streaming

Results stream to the client as Arrow record batches while the query runs. Read large results to the end, or close them promptly, so the query's resources are released.

The HTTP SQL API streams POST /v1/sql results in the requested format. Asynchronous statements return small results inline and larger ones as links to Arrow chunks; see asynchronous statements.

Streaming limits client buffering; it does not limit the work upstream. Constrain production queries with selective predicates and bounded output.

Cancellation

Cancellation is cooperative across the distributed execution and can take a moment while remote reads reach a safe boundary. A cancelled statement reports QUERY_CANCELLED. The session remains usable unless its transaction was aborted.

Sensitive statements

CREATE PERSISTENT SECRET deliberately cannot be prepared or executed inside a transaction. Run it alone, as a direct statement over TLS. VegaDB redacts it from service-side history, but the client remains responsible for its own terminal, notebook and CI history.