Transactions and Savepoints

KalamDB transactions let you commit related writes together or roll them back. Use a persistent PostgreSQL connection for interactive transactions; keep an HTTP transaction in one request.

Run a KalamDB transaction

Create the table once before the transaction:

1CREATE SCHEMA IF NOT EXISTS app;2CREATE TABLE app.tasks (3  id BIGSERIAL PRIMARY KEY,4  title TEXT NOT NULL,5  completed BOOLEAN DEFAULT false6) WITH (TYPE = 'USER');7 8BEGIN;9INSERT INTO app.tasks (title) VALUES ('Write the guide') RETURNING id;10SAVEPOINT before_edit;11UPDATE app.tasks SET completed = true WHERE title = 'Write the guide';12ROLLBACK TO SAVEPOINT before_edit;13RELEASE SAVEPOINT before_edit;14COMMIT;15SELECT title, completed FROM app.tasks;

The insert commits; the update after the savepoint is undone. An open transaction does not see commits that arrive after it starts.

Recover from an error

A statement error inside an explicit transaction puts it into a failed state. Later ordinary commands fail with SQLSTATE 25P02. Use ROLLBACK, or ROLLBACK TO SAVEPOINT when a valid savepoint exists, before continuing. Unknown savepoints return 3B001.

Connection and request scope

PGWire keeps one transaction state per connection. With a pool, acquire one client for the whole transaction and release it after commit or rollback. Independent pool queries may use different connections.

HTTP requests do not share transaction state. Put BEGIN, dependent statements, and COMMIT in one /v1/api/sql request. Avoid splitting them across SDK query() calls.

Transactions that span different Raft groups are rejected. Do not design a cross-group write as one atomic transaction. SERIALIZABLE and READ UNCOMMITTED are rejected with SQLSTATE 0A000.

Returning changed rows

INSERT, UPDATE, and DELETE support RETURNING. Return only the columns your application needs:

1UPDATE app.tasks SET completed = true2WHERE title = 'Write the guide'3RETURNING id, completed;

Next: Query plans. Reference: PostgreSQL compatibility.