Support atomic commits spanning local and nonlocal tables

[off-axis]

Please add a supported way for one SQL transaction to modify versioned tables on the active branch and writable nonlocal tables targeting another local branch, then commit both sets of changes atomically.

Doltgres currently rejects this transaction with:

ERROR: Cannot commit changes on more than one branch / database

The current documentation only supports options = 'immediate'. A manual or deferred mode is insufficient unless its publication operation participates in that same atomic commit.

Repro

Reproduced with dolthub/doltgresql:1.3.0:

CREATE TABLE local_items (
    id integer PRIMARY KEY,
    value text NOT NULL
);

CREATE TABLE global_items (
    id integer PRIMARY KEY,
    value text NOT NULL
);

INSERT INTO dolt_nonlocal_tables (
    table_name,
    target_ref,
    ref_table,
    options
) VALUES (
    'global_items',
    'main',
    'global_items',
    'immediate'
);

SELECT dolt_commit('-Am', 'set up local and nonlocal tables');
SELECT dolt_branch('feature', 'HEAD');
SELECT dolt_checkout('feature');

BEGIN;
INSERT INTO local_items VALUES (1, 'local');
INSERT INTO global_items VALUES (1, 'nonlocal');
COMMIT;

The final COMMIT fails:

ERROR: Cannot commit changes on more than one branch / database

The server correctly rolls back both inserts, but there is no supported mode in which the transaction can succeed atomically. The same failure occurs when dolt_transaction_commit is enabled.

Use case

The app we're working on stores application-wide metadata on main through nonlocal tables while keeping versioned data on per-user branches. Operations such as versioned data creation/edit, metadata edit, etc update both nonlocal tables and versioned tables in users' branches.

Splitting these operations into separate transactions in certain cases can leave global metadata describing a state that the branch never committed, or advance the branch without publishing its corresponding metadata.

Required behavior

For our app, supported nonlocal-table mode is required with these semantics:

• Before COMMIT, neither write is visible outside the transaction.
• On success, the local write is applied to the active branch and the nonlocal write is applied to its target branch.
• With dolt_transaction_commit = on, advancing the active branch revision is part of the same atomic outcome.
• If either destination conflicts or fails, neither working set changes and no branch revision advances.
• Concurrent transactions touching the same nonlocal target participate in conflict detection.

It is acceptable for a manual mode to require an explicit push or publication call, provided that call can be included in the same atomic transaction as the active branch commit.

[fulghum]

Hey @off-axis, thank you for the enhancement request! I agree this would be a cool feature. The implementation is tricky, but it's something we've discussed before.