COMMIT

COMMIT ends the current transaction. Upon the COMMIT statement:

• If all transaction statements succeed, all changes are committed.

• If an error occurs, all changes are discarded; i.e., rolled back.

Syntax

COMMIT;

Details

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.

Transactions in Materialize are either read-only transactions or write-only (more specifically, insert-only) transactions.

For a write-only (i.e., insert-only) transaction, all statements in the transaction are committed at the same timestamp.

Examples

Commit a write-only transaction

In Materialize, write-only transactions are 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

Commit a read-only transaction

In Materialize, read-only transactions can be either:

• a SELECT only transaction that only contains SELECT statements or

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

For example:

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;

During the first query, a timestamp is chosen that is valid for all of the objects referenced in the query. This timestamp will be used for all other queries in the transaction.

See also

• BEGIN
• ROLLBACK