BEGIN

BEGIN starts a transaction block. Once a transaction is started:

• Statements within the transaction are executed sequentially.

• A transaction ends with either a COMMIT or a ROLLBACK statement.

  • If all transaction statements succeed and a COMMIT is issued, all changes are saved.

  • If all transaction statements succeed and a ROLLBACK is issued, all changes are discarded.

  • If an error occurs and either a COMMIT or a ROLLBACK is issued, all changes are discarded.

Materialize only supports read-only transactions or write-only (specifically, insert-only) transactions. See Details for more information.

Syntax

BEGIN [ <option>, ... ];

You can specify the following optional settings for BEGIN:

Details

Transactions in Materialize are either read-only transactions or write-only transactions as determined by either:

• The first statement after the BEGIN, or

• The READ ONLY option is specified.

Read-only transactions

In Materialize, read-only transactions can be either:

• a SELECTonly transaction that only contains SELECT statements or

• a SUBSCRIBE-based transactions that only contains a single DECLARE ... CURSOR FOR SUBSCRIBE statement followed by subsequent FETCH statement(s). ¹

SELECT-only transactions

A SELECT-only transaction only contains SELECT statement.

The first SELECT statement:

• Determines the timestamp that will be used for all other queries in the transaction.

• Determines which objects can be queried in the transaction block.

Specifically,

• Subsequent SELECT statements in the transaction can only reference objects from the schema(s) referenced in the first SELECT statement (as well as a subset of objects from the mz_catalog and mz_internal schemas).

• These objects must have existed at beginning of the transaction.

For example, in the transaction block below, first SELECT statement in the transaction restricts subsequent selects to objects from test and public schemas.

BEGIN;
SELECT o.*,i.price,o.quantity * i.price as subtotal
FROM test.orders as o
JOIN public.items as i ON o.item = i.item;

-- Subsequent queries must only reference objects from the test and public schemas that existed at the start of the transaction.

SELECT * FROM test.auctions limit 1;
SELECT * FROM public.sales_items;
COMMIT;

Reading from a schema not referenced in the first statement or querying objects created after the transaction started (even if in the allowed schema(s)) will produce a Same timedomain error. Same timedomain error provides a list of the allowed objects in the transaction.

Same timedomain error

Transactions can only reference objects in the same timedomain.

The first SELECT statement in a transaction determines which schemas the subsequent SELECT statements in the transaction can query. If a subsequent SELECT references an object from another schema or an object created after the transaction started, the transaction will error with the same time domain error.

The timedomain error lists both the objects that are not in the timedomain as well as the objects that can be referenced in the transaction (i.e., in the timedomain).

If an object in the timedomain is a view, it will be replaced with the objects in the view definition.

SUBSCRIBE-based transactions

A SUBSCRIBE-based transaction only contains a single DECLARE ... CURSOR FOR SUBSCRIBE statement followed by subsequent FETCH statement(s). ¹

BEGIN;
DECLARE c CURSOR FOR SUBSCRIBE (SELECT * FROM flippers);

-- Subsequent queries must only FETCH from the cursor

FETCH 10 c WITH (timeout='1s');
FETCH 20 c WITH (timeout='1s');
COMMIT;

Write-only transactions

In Materialize, a write-only transaction is an INSERT-only transaction that only contains INSERT statements.

INSERT-only transactions

An insert-only transaction only contains INSERT statements that insert into the same table.

On a successful COMMIT, all statements from the transaction are committed at the same timestamp.

BEGIN;
INSERT INTO orders VALUES (11,current_timestamp,'brownie',10);

-- Subsequent INSERTs must write to sales_items table only
-- Otherwise, the COMMIT will error and roll back the transaction.

INSERT INTO orders VALUES (11,current_timestamp,'chocolate cake',1);
INSERT INTO orders VALUES (11,current_timestamp,'chocolate chip cookie',20);
COMMIT;

If, within the transaction, a statement inserts into a table different from that of the first statement, on COMMIT, the transaction encounters an internal ERROR and rolls back:

ERROR:  internal error, wrong set of locks acquired

See also

• COMMIT
• ROLLBACK