During a query that references any remote tables on a foreign server,postgres_fdwopens a transaction on the remote server if one is not already open corresponding to the current local transaction. The remote transaction is committed or aborted when the local transaction commits or aborts. Savepoints are similarly managed by creating corresponding remote savepoints.
The remote transaction usesSERIALIZABLEisolation level when the local transaction hasSERIALIZABLEisolation level; otherwise it usesREPEATABLE READisolation level. This choice ensures that if a query performs multiple table scans on the remote server, it will get snapshot-consistent results for all the scans. A consequence is that successive queries within a single transaction will see the same data from the remote server, even if concurrent updates are occurring on the remote server due to other activities. That behavior would be expected anyway if the local transaction usesSERIALIZABLEorREPEATABLE READisolation level, but it might be surprising for aREAD COMMITTEDlocal transaction. A futurePostgreSQLrelease might modify these rules.
FDW for PostgreSQL never emit transaction command such as BEGIN, ROLLBACK and COMMIT. Thus, all SQL statements are executed in each transaction when 'autocommit' was set to 'on'.
