Branching for Agents
By the end of this page you will have given an agent an instant, disposable, full copy of real data to work against, watched it work without touching your source database, and cleaned up after it, start to finish.
1. Start with a real database
Assume you already have a database with real data in it, for example the sensors table from Quick Start:
CREATE TABLE sensors (
id BIGINT PRIMARY KEY,
device_id INTEGER NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
recorded_at TIMESTAMP
);
INSERT INTO sensors VALUES
(1, 101, 22.5, 45.0, '2024-03-15 10:00:00'),
(2, 101, 23.1, 44.0, '2024-03-15 10:05:00'),
(3, 102, 19.8, 55.0, '2024-03-15 10:00:00'),
(4, 102, 20.1, 54.0, '2024-03-15 10:05:00'),
(5, 103, 25.3, 38.0, '2024-03-15 10:00:00');
2. Give the agent a branch
Connected to the default scramdb database, run:
CREATE DATABASE agent_run_142 CLONE scramdb;
Expected result: a bare success response, no rows. CREATE DATABASE ... CLONE is instant regardless of the source database's size, because every existing storage segment is shared by reference between the clone and its source, not copied. You are not waiting on a copy job; the clone exists the moment the statement returns.
Verify by connecting to the new branch (see Connecting an Agent for the exact connection forms) and checking the data is already there:
-- after connecting with dbname=agent_run_142
SELECT COUNT(*) FROM sensors;
Expected: 5, the same row count the source had at the moment you ran CLONE.
If it fails: CREATE DATABASE runs outside of an explicit transaction block; if you are inside one (BEGIN already issued), commit or roll it back first and re-run the statement.
3. Scope the agent to its own role and branch
Give the agent a role, and grant it only what it needs on the branch:
-- connected to agent_run_142
CREATE ROLE agent_run_142_role LOGIN PASSWORD 'a-real-password';
GRANT SELECT, INSERT, UPDATE ON sensors TO agent_run_142_role;
Both statements run on the branch connection. CREATE ROLE is instance-wide (roles are shared across databases, PostgreSQL semantics), while the GRANT is scoped to the branch: it covers the branch's sensors only and grants nothing on the source database's table of the same name. Granting an agent access on its branch never widens access to production.
Connect the agent as agent_run_142_role against dbname=agent_run_142, exactly as shown in Connecting an Agent.
The GRANT covers sensors as it exists now. For the tables the agent creates later, set the branch's default privileges before it starts, so each new table is readable by the roles that review its work:
-- connected to agent_run_142
CREATE ROLE reviewer;
ALTER DEFAULT PRIVILEGES FOR ROLE agent_run_142_role IN SCHEMA public GRANT SELECT ON TABLES TO reviewer;
CREATE AGENT does steps 2 and 3 in one statement (the branch, its role, a quota and excluded tables); see Semantic AI.
4. Let the agent do whatever it likes
This is the point of the branch: nothing on it is precious. The agent can bulk-load with COPY (including COPY ... FROM STDIN), run bulk updates or a MERGE, refresh statistics with ANALYZE, drop and rebuild an index, TRUNCATE a table, or run an exploratory ALTER TABLE, and none of it touches the source database.
-- connected to agent_run_142
UPDATE sensors SET temperature = temperature + 1.0 WHERE device_id = 101;
DELETE FROM sensors WHERE humidity < 40.0;
ALTER TABLE sensors ADD COLUMN reviewed BOOLEAN DEFAULT false;
5. Promote or discard
To keep what the agent produced, merge the branch back into its parent. Connect to the parent, look first, then merge:
-- connected to scramdb, the parent
-- 1. what the merge would do, table by table, writing nothing
SELECT * FROM scram.merge_preview('agent_run_142');
-- 2. the same answer as the merge would give, still writing nothing
MERGE DATABASE agent_run_142 INTO scramdb WITH (dry_run = on);
-- 3. merge the branch's inserts, updates, deletes and schema changes since it was made
MERGE DATABASE agent_run_142 INTO scramdb; -- on_conflict = fail
-- or, as a function an agent's orchestrator calls, here letting the branch win a conflict
SELECT * FROM scram.merge_branch('agent_run_142', on_conflict => 'branch');
| Option | Effect |
|---|---|
on_conflict = fail | The default. A conflict applies nothing and every conflict is listed. |
on_conflict = branch | The branch's change wins. |
on_conflict = parent | The parent's row stays; the branch's change is reported as skipped. |
on_conflict = 'function:schema.name' | Your function picks the row (below). |
dry_run = on | Answers what the merge would do and writes nothing. |
drop_branch = on | Drops the branch once the merge commits. Other sessions on the branch get five seconds to leave; if one stays, the merge answers 55006 before it merges anything and the branch is as it was. |
merge_id = 'id' | Names the merge, so a retry after a lost connection changes nothing. |
Every form, refresh, reset and swap included, has its example in the SQL reference.
A branch merges into the database it was cut from or into any database above that. A snapshot is a clone of scramdb you keep, and an agent branch cut from it merges into scramdb from a session connected to scramdb. The merge sends what each database on the way wrote before the next one was cut from it, together with the branch's own changes; what a database on the way writes after that stays with it. A key that both sides changed since they last agreed (the cut of the first database below scramdb, then each merge of the branch) is a conflict, settled by on_conflict as for a parent.
Such a merge changes the target only. No database on the way is touched and no base moves, so a second merge of the branch sends only what is new on either side. A dry run, scram.merge_preview and drop_branch = on work as for a parent, and drop_branch drops the branch and leaves the snapshot. A refresh or a reset still takes the parent alone.
It is refused with 55000, in a message that names the databases and the merge to make first, when:
- a database on the way merged into its own parent or refreshed from it after the next one was cut from it, or one of its refreshes settled keys with a conflict function: merge the branch into that database first;
- the branch, or a database on the way below the first, already merged into its own parent: merge that parent into its own parent first;
- the database right below the target merged into the target or refreshed from it since the branch last merged into the target: merge the branch into that database first.
A database that is not above the branch is refused 42809, and a merge past a database that was dropped 55000. On a cluster a branch merges only into its own parent (0A000).
What a merge does:
- Sends what changed since the base, the moment the branch was made or its last merge or refresh: rows and schema.
- Schema first. A table, column or index the branch created or dropped is created or dropped on the parent, with its row level security, policies, grants, comments, triggers and sequences, and so is a function, procedure, aggregate, view or materialized view the branch created, replaced or dropped (an aggregate after its state and final functions; a materialized view is built again on the parent from the merged rows, inside the merge). The answer's
destructivecolumn marks a drop. A schema change both sides made differently is a conflict. A conflict function settles rows only: while a schema conflict stands, a merge in function mode applies nothing and lists it. A column added, dropped, renamed or changed in type merges into the default database and into one made withCREATE DATABASEalike. - A renamed column is a rename. A column renamed on the branch (
ALTER TABLE sensors RENAME COLUMN humidity TO humidity_pct) reaches the parent as a rename, not as one column dropped and another added: it keeps every value, and the schema lines in the table'sconflict_detailreadrename column humidity to humidity_pct. The preview, a dry run and the merge say the same, and so does a refresh that takes in a rename the parent made. A column is told from another by the place it holds in its table, not by its name, so a rename beside a change of the column's type merges as both, and a renamed primary key column stays the key. A column the parent dropped is a schema conflict as above, and a rename both sides made to the same name changes nothing. A table dropped and made again on the branch is a new table whatever its columns are called: it arrives with the columns and rows the branch holds. A name the branch reuses for a new column beside a rename keeps both columns' values, and a check, an index or a row security policy that names the renamed column is on the new name after the merge. In a branch made by an earlier release, before the engine recorded the place of each column, a renamed column merges as one dropped and another added. - Sequences catch up. A sequence of the parent that is behind the branch's moves to the branch's position, so the parent's next insert never takes a key the merge brought in. It never moves back.
- One transaction, under the merging role's privileges and the parent's constraints, indexes and row security. A failure at any step leaves the parent as it was.
- Rows match by key. By the primary key, else by the first (by name) unique index whose columns are all plain
NOT NULLcolumns (not partial, not on an expression, not deferrable), else by the whole row. A key the parent also changed since the base is a conflict. - A table matched by its whole row never conflicts. Each row the branch added is added to the parent, even when the parent holds an equal row, because equal rows are rows of their own. Each row the branch removed takes one equal row away from the parent if it has one, and nothing if it does not. A row the branch added and removed again, or removed and added back, is no change. Equal means every column holds the same stored value: NULL equals NULL, NaN equals NaN, a negative zero is not a zero, text matches byte for byte and a numeric matches by its value (
1.0and1.00are one). The answer counts inserts and deletes only, an update being a delete and an insert, and a refresh and an undo of such a table work the same way. A merge that has to remove rows scans the table once, because no index finds them. - A new
NOT NULLcolumn without a default lands once every row holds a value. A parent row that would stay empty is a conflict, and nothing is applied. - The answer is one row per table (
table,inserted,updated,deleted,conflicts) with the merge id, recorded in thescram_branch_mergesof the database merged into (the merge record).
A conflict function has the signature (table text, key jsonb, base jsonb, branch jsonb, parent jsonb) RETURNS jsonb. It gets the row as it was at the base and as each side has it now (NULL where absent), and returns the row to keep, or NULL to delete it. It runs in batches as the merging role, who needs EXECUTE on it (42501). A returned row that does not fit the table is refused (22023) and nothing is applied. It works for a merge, a refresh and an agent's scram.merge_request.
The merge record
scram_branch_merges holds one row for every table of each merge or refresh a database recorded. It is read live and takes every operator a table takes. After the merge above, from a connection to the parent:
-- connected to scramdb, the parent
SELECT merge_id, kind, branch, "table", inserted, updated, deleted, conflicts
FROM scram_branch_merges ORDER BY "table";
table is a reserved word, so a query writes it in double quotes.
| Column | Type | Meaning |
|---|---|---|
merge_id | TEXT | The merge's id, as the answer gives it. |
kind | TEXT | merge, or refresh. A refresh is recorded in the branch's own database. |
branch | TEXT | The branch's name, NULL once the branch is dropped (drop_branch = on does it). |
branch_oid | BIGINT | The oid of the branch's database. |
target | TEXT | The database that holds the record: the one merged into, or for a refresh the branch itself. |
table | TEXT | The table, with its schema (public.sensors). |
inserted, updated, deleted, conflicts | BIGINT | The counts of the answer's row for the table. |
destructive | BOOLEAN | Whether the answer marked the table's change as a drop. Never NULL. |
merged_to | BIGINT | The commit position the merge or refresh read up to. |
committed_at | BIGINT | The commit position of the merge or refresh, NULL while it has not committed. |
The newest merge's merge_id and committed_at are the last_merge_id and last_merge_position that scram.branch_info answers for the branch.
A superuser sees every row. Any other role sees the rows of a merge whose branch it owns or may CONNECT to, and every row of the database it is connected to when it owns that database or may CONNECT to it (a member of an owning role counts as its owner). PUBLIC holds CONNECT until it is revoked, so by default every role that can connect sees them. A row holds the counts and the positions, never the keys of a conflict or what an undo needs.
Refresh or reset the branch
A refresh is a rebase: it brings the parent's newer changes into the branch. A reset throws the branch's own changes away and starts it again from the parent.
-- connected to agent_run_142
REFRESH DATABASE agent_run_142 FROM scramdb; -- scram.refresh_branch('agent_run_142')
-- connected to scramdb
RESET DATABASE agent_run_142; -- scram.reset_branch('agent_run_142')
SELECT * FROM scram.promote_branch('agent_run_142'); -- a merge where the branch wins every conflict
SELECT * FROM scram.branch_info('agent_run_142'); -- parent, fork position, merge base, last merge and refresh
SELECT * FROM scram.branches(); -- the branches of this database
- A refresh moves the branch's base forward and keeps its own work: the next merge still sends every change the branch made, before the refresh or after it. A branch change made before a refresh still conflicts with a later parent change to the same key.
- A reset is refused (
55006) while other sessions are connected to the branch. - Merge, refresh and reset need ownership of the branch (or membership of its owner) and
CONNECTon both databases (42501). - A merge, a refresh, a dry run,
scram.merge_previewandscram.promote_branchare also judged table by table for the role that runs them, before a row is read:SELECTon the tables they read,INSERT,UPDATEandDELETEon the other database as the changes need, and a table whose row-level security applies to the role is refused. A superuser, and a role that owns every database of the merge, are not judged table by table. The refusals are on MERGE DATABASE. scram.branch_infoneedsCONNECTon the branch (42501), andscram.branches()lists the branches of this database that the role owns or may connect to.- On a cluster they run from any node, and a merge commits once on every node.
Merge requests: the agent asks, a person applies
An AI agent role does not merge its own branch. It asks for a merge, a person approves it, and the person applies it with the one-time token the approval answers:
-- the agent, connected to its branch
REQUEST MERGE OF DATABASE agent_run_142; -- request_id and the preview, table by table
-- a superuser, the parent's owner or a member of pg_agent_admin
APPROVE MERGE REQUEST 'mr-...'; -- request_id, approval_token, expires_at (shown once)
APPLY MERGE REQUEST 'mr-...' USING TOKEN '<approval_token>'; -- the merge's answer
SHOW MERGE REQUESTS; -- every request and its status
The functions are scram.merge_request('agent_run_142'), scram.approve('mr-...'), scram.merge_apply('mr-...', '<approval_token>') and scram.merge_requests().
- An agent is refused
scram.approveandscram.merge_apply(42501). - The preview shows, per table, up to
[agent] merge_preview_sample_rowsrows the merge would change or delete, before and after, in itssamplecolumn. - The approval token works once, for that request only, until
[agent] approval_token_ttl. - If the branch changed after the approval, the apply is refused (
55000): make a new request. - A merge that deletes more than
[agent] destructive_delete_fractionof a table's rows is refused (55000) unless the call passesallow_destructive => true(ALLOW DESTRUCTIVE).
Undo a merge
UNDO MERGE REQUEST '<request id>'; -- per table: deleted, restored, reinserted, conflicts, base_restored
UNDO MERGE REQUEST '<request id>' WITH (on_conflict = force); -- restore keys changed again since the merge too
-- scram.merge_undo('<request id>'), scram.merge_undo('<request id>', on_conflict => 'force')
- An undo takes back only what the merge wrote. An inserted row is deleted, a changed row gets its earlier values back, a deleted row comes back, and its schema changes are undone before its rows. Every other write since the merge stays.
- A row or column changed again since the merge is a conflict: nothing is applied and the conflicts are listed, unless
on_conflict => 'force'. - The branch goes back to where it was before that merge, so merging again sends the same changes. If it was merged or refreshed since, its base stays put (
base_restoredfalse, with the reason). - The people who may apply a merge may undo it; an agent is refused (
42501). It works on any database, the default one included, with sessions connected. - An undo needs the merge's pre-apply snapshot, kept for
[agent] preapply_snapshot_retention(55000after that).
Discard the branch
When the run ends, drop the branch. It is the common case:
-- from a connection to a database OTHER than agent_run_142; you cannot
-- drop the database you are currently connected to
DROP DATABASE agent_run_142;
The drop frees only what the agent wrote. Storage the branch still shares with its parent stays untouched, so dropping ten agent branches costs what the ten agents changed, not ten times the parent's size.
If it fails: the server's default database cannot be dropped, and the attempt is refused by name.
6. Automate the loop
Steps 2 through 5 are a repeatable unit: clone, scope a role, let the agent work, drop. See Agent Patterns for how this loop fits into guardrails around agent-written SQL, and Using Graphs for building and discarding a speculative graph the same way.
A branch has no read-only mode today. Give it an expiry (CREATE DATABASE ... CLONE ... WITH (expire_after = '1 day') or scram.expire_branch) and the server drops it once the time passes, or drop it yourself when the run ends; see Cleaning up a branch.