When one PostgreSQL transaction writes to local tables and to postgres_fdw foreign tables, what isolation and commit guarantees does the remote side actually get?
answer
- a remote transaction shadows the local one
- never READ COMMITTED remotely
- the remote COMMIT goes first
- no PREPARE TRANSACTION
- dblink runs on its own
basics
~20 spostgres_fdw opens a remote transaction at REPEATABLE READ (SERIALIZABLE if the local one is) and sends the remote COMMIT just before the local one. With no two-phase commit, a failure between those steps leaves one side committed and the other not.
solid answer
~40 sOn first use of a foreign server inside a local transaction, postgres_fdw starts a remote transaction, `REPEATABLE READ` unless the local one is `SERIALIZABLE`, and mirrors savepoints as remote savepoints. So even a local `READ COMMITTED` transaction sees one unchanging remote snapshot across its statements. At local commit, postgres_fdw sends `COMMIT` to each remote server in the pre-commit phase, one server after another unless `parallel_commit` is set, before the local commit record is written. There is no two-phase commit: `PREPARE TRANSACTION` fails once foreign tables were touched, and if a second server's `COMMIT` fails, or the local commit fails after the remote one succeeded, the sides disagree. Design for that with idempotent, reconcilable writes. `dblink_exec` is weaker still: its statements commit on the remote session independently of the local transaction.
code
sql · 15 linesBEGIN;
-- local: record the write-off in the finance ledger
INSERT INTO finance.write_offs (invoice_id, amount, booked_at)
VALUES (48213, 120.00, now());
-- remote: mark the invoice on the billing server
UPDATE billing_remote.invoices
SET status = 'written_off'
WHERE invoice_id = 48213;
-- is the billing connection still alive before we commit?
SELECT server_name, used_in_xact, closed
FROM postgres_fdw_get_connections(true);
COMMIT; -- the billing server commits first, then the finance databasego deeper
Recall that postgres_fdw opens a remote transaction that commits or rolls back together with the local one, and that it is not two-phase commit.
Explain the REPEATABLE READ remote isolation, mirrored savepoints, and the order of the remote and local commits.
Walk through each failure window between the remote and local commits, show why PREPARE TRANSACTION is refused, and design idempotent, reconcilable cross-server writes.
Decide whether a cross-server write belongs in one transaction at all, or whether one owner should write and the other consume the change asynchronously.
## How postgres_fdw maps one transaction onto two servers The finance database books a write-off locally and marks the invoice written off on the billing server, in one `BEGIN ... COMMIT`. postgres_fdw handles the remote half like this: 1. **Connection.** On the first query that touches a billing foreign table, postgres_fdw opens or reuses this session's connection for that server and user mapping. Connections are kept for the session by default (`keep_connections`). 2. **Remote begin.** It sends `START TRANSACTION ISOLATION LEVEL REPEATABLE READ`, or `SERIALIZABLE` when the local transaction is `SERIALIZABLE`. 3. **Savepoints.** When remote work happens inside a local savepoint, including the implicit one of a PL/pgSQL exception block, postgres_fdw creates a matching remote savepoint, so a local `ROLLBACK TO SAVEPOINT` undoes that remote work too. 4. **Pre-commit.** When you `COMMIT`, postgres_fdw sends `COMMIT TRANSACTION` to every remote server involved, serially by default. 5. **Local commit.** Only then does PostgreSQL write the local commit record. On a local abort, postgres_fdw aborts every remote transaction instead. ## Isolation consequences - **Stable remote snapshot.** Because the remote side is never `READ COMMITTED`, every statement in the local transaction sees the billing data as of its first remote query. The manual calls this out as surprising for a local `READ COMMITTED` transaction. The reason is consistency: a query that scans the remote server several times gets one snapshot. - **Remote serialization errors surface locally.** If the billing row you `UPDATE` was changed and committed by someone else after your remote snapshot, the remote `REPEATABLE READ` transaction fails with SQLSTATE `40001`, and postgres_fdw re-raises the remote error code locally. The retry loop for that error is the same as for any `REPEATABLE READ` work. - **Long transactions cost the remote side too.** A finance session that opens a transaction, touches a billing table and then sits idle holds an open `REPEATABLE READ` transaction on the billing server for as long as it stays open. That old snapshot holds back cleanup of dead rows there, which the billing team will notice before you do. Keep cross-server transactions short, and commit read-only reports as soon as they finish. ## Where atomicity breaks The remote commits happen before the local one, and nothing ties them together: | Failure point | Billing server A | Billing server B | Finance database | |---|---|---|---| | Error before `COMMIT` | rolled back | rolled back | rolled back | | A commits, B's `COMMIT` fails (e.g. a deferred constraint) | **committed** | rolled back | rolled back | | Remote commits succeed, then a local step fails (a `SERIALIZABLE` check, a crash before the commit record) | **committed** | **committed** | rolled back | | Connection lost mid-transaction | rolled back by the remote server | - | errors, rolls back | The fix is not a setting. Make the remote write idempotent (mark the invoice written off by key, not by delta), record what you intend to do so a job can reconcile, or move the write to one side and let the other learn about it asynchronously. Why distributed atomic commit is hard in general is a separate, engine-neutral question. ## No two-phase commit postgres_fdw cannot prepare a remote transaction. If you run `PREPARE TRANSACTION` after touching a postgres_fdw foreign table, it fails with `cannot PREPARE a transaction that has operated on postgres_fdw foreign tables`. So a transaction manager that drives two-phase commit cannot include postgres_fdw writes. ## Operational tools - **`parallel_commit` and `parallel_abort`** (server options, default false) commit or abort the remote transactions of several servers concurrently. That is faster, but no more atomic. - **`postgres_fdw_get_connections(true)`** lists this session's connections. PostgreSQL 18 added the `used_in_xact`, `closed`, `user_name` and `remote_backend_pid` columns. On platforms with `POLLRDHUP`, such as Linux, `check_conn => true` fills `closed`, so you can roll back early instead of failing at commit. - **`postgres_fdw.application_name`** accepts escapes such as `%p` and `%u`, so the billing team can tell which finance backend owns a remote session. - **Wait events** `PostgresFdwConnect`, `PostgresFdwGetResult` and `PostgresFdwCleanupResult` (type `Extension`) show a local backend waiting on the remote server. ## dblink is different `dblink` keeps its own named or unnamed connections and does not follow the local transaction. A `dblink_exec` statement runs on the remote session as its own statement and commits there unless you sent `BEGIN` over that connection yourself, so rolling back the local transaction does not undo it. `dblink_open` starts a remote transaction block only to keep a cursor alive until `dblink_close`.
- Why does a READ COMMITTED transaction in the finance database not see billing rows committed while it runs?postgres_fdw runs the remote transaction at `REPEATABLE READ` whatever the local level, unless the local one is `SERIALIZABLE`. The remote snapshot is taken at the first remote query and kept for the whole local transaction. Commit and start a new transaction to see newer billing data.
- What happens if you run PREPARE TRANSACTION after touching a postgres_fdw foreign table?It fails with `cannot PREPARE a transaction that has operated on postgres_fdw foreign tables`. postgres_fdw has no way to prepare the remote side, so it refuses rather than pretend the transaction is safely prepared. Two-phase commit across servers is therefore unavailable through postgres_fdw.
- How do dblink_exec's guarantees differ from postgres_fdw's?postgres_fdw ties a remote transaction to the local one and commits or aborts it with the local transaction. `dblink_exec` runs on its own remote connection, which commits each statement unless you sent `BEGIN` over it yourself, so a local `ROLLBACK` leaves the remote change in place.
- postgres_fdw uses two-phase commit, so the local and remote commits are atomic.
- The remote side runs at READ COMMITTED whenever the local transaction does.
- Rolling back the local transaction undoes statements sent with dblink_exec.
- parallel_commit makes the commits across several servers atomic.
- The local commit happens first, and postgres_fdw commits the remote side afterwards.
Assumes PostgreSQL 18; postgres_fdw_get_connections() gained its used_in_xact, closed, user_name and remote_backend_pid columns in 18.