Merge checks

#Why checks

A merge policy judges the change (how many rows, which tables). A merge check judges the data: a query that must find nothing in what the merge would make. Every merge runs the checks for the tables it changes on the result, inside the merge, under its locks, before anything is written. If one finds rows, the merge is refused with those rows as the reason, and nothing merges.

Because the merged result is checked, two changes that are each fine alone but break the rule together are caught: one agent moves the last lead out of a list while another empties the list’s other leads.

create merge check lists_keep_leads on tables (leads, lists) as  select id from lists where id not in (select list from leads);merge world cleanup;-- ERROR:  merging cleanup breaks merge check lists_keep_leads, which finds (id = 3); nothing was merged

#Statements

Only an admin creates or drops checks (you, in the dashboard); any agent may list them (MCP merge_checks). A check’s definition lives in the database, so a merge that brings one in is a schema change under a merge policy.

#What a check runs on

• The world merged into, as the merge would leave it: with ONLY TABLES or ONLY KEYS, only those rows merged; with INTO, the other world.
• Which merges: with ON TABLES, merges that change one of those tables’ rows or the table itself; without, every merge.
• Not direct writes: a transaction’s COMMIT, or your app writing main, isn’t a merge. Use constraints for rules every write must keep.
• No one skips a check. A person’s merge is refused like an agent’s. A person approves a queued world by changing it until it passes; an exception is made by changing the check.
• Dry runs run the checks too: the blocked row names the checks that would fail and the rows they find.
• For an agent with a policy, a broken check is a broken rule: its world is queued in Reviews with the rows found, not just refused.

#Writing a check

• One SELECT (or WITH … SELECT) that changes nothing: no nextval, setval, pg_notify, and no function made with CREATE FUNCTION in it or a view it reads (25006).
• Return the rows that are wrong, with a column that names them (an id): they become the error’s reason.
• It runs read only, as the database itself, with the default search_path (public): name tables in other schemas in full (app.orders).
• Table names in ON TABLES are read as SQL reads them; one that isn’t a table on main is refused (42P01).

#Cost and timeouts

A check runs within its timeout (default 1 second): a merge it doesn’t finish in is refused, since the merge waits for it holding its locks. It stops at the 11th row it finds, and the reason shows the first 10. Index the columns a check filters on, and scope it with ON TABLES so unrelated merges don’t run it.

#Examples

create merge check order_totals on tables (orders, order_lines) as  select o.id from orders o join order_lines l on l.order_id = o.id  group by o.id, o.total having sum(l.qty * l.price) <> o.total;

create merge check no_negative_balance on tables (accounts) as  select id from accounts where balance < 0;

create merge check subs_have_payment on tables (subscriptions, payment_methods) as  select s.id from subscriptions s  where s.status = 'active'    and not exists (select 1 from payment_methods p where p.customer_id = s.customer_id);

create merge check one_primary_address on tables (addresses) with (timeout = '2s') as  select customer_id from addresses where is_primary group by customer_id having count(*) > 1;

#Checks, constraints and policies