Transactions and parameters

Transaction contract

The PostgreSQL endpoint supports explicit and implicit read-only transactions at READ COMMITTED.

BEGIN READ ONLY;
SELECT count(*) FROM sales.orders;
SELECT max(ordered_at) FROM sales.orders;
COMMIT;

Each statement in a read-only READ COMMITTED transaction resolves an authorized snapshot according to the selected catalogs. A multi-catalog query resolves all of its table versions before execution.

Form	Supported behavior
Implicit transaction	A statement outside `BEGIN` runs in its own transaction boundary.
`BEGIN [TRANSACTION] READ ONLY`	Starts an explicit read-only block.
`COMMIT`	Ends a successful block.
`ROLLBACK`	Ends the block and clears failed-transaction state.
Failed statement	Later statements fail until `ROLLBACK` or the block ends.
Savepoints	Not supported.
Read-write transaction through PostgreSQL endpoint	Not supported.
Repeatable read / serializable / deferrable	Not supported.
Two-phase commit	Not supported.

Catalog-managed write statements, when available, run through their admitted managed-write contract. Do not wrap them in a PostgreSQL read-only transaction.

Prepared parameters

PostgreSQL extended-query clients use $1, $2, and so on. A parameter's type comes from the prepared signature, an explicit cast or surrounding expression.

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

Bind values through the driver API. Do not interpolate strings into SQL.

NULL parameters

An untyped NULL is ambiguous when no surrounding expression determines its type:

SELECT $1::UUID AS optional_customer_id;

For nested input, bind canonical JSONB and cast it to the complete target:

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

Portals and result streaming

A client can bind a prepared statement to a portal and execute it with a maximum row count. VegaDB suspends the portal when that count is reached and resumes it on a later execute. Set the driver's fetch size for large results and close results promptly.

Portal suspension limits client buffering; it does not guarantee that every upstream stage stops holding intermediate state. Always constrain production queries with selective predicates and bounded output.

Cancellation

PostgreSQL cancellation requests target an active query. Cancellation is cooperative across the distributed execution and may take time while remote reads or committed output steps reach a safe boundary. The session remains usable after cancellation unless the surrounding transaction has entered failed state.

Sensitive statements

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