RE: Transactions involving multiple postgres foreign servers, take 2

Hi Sawada san,

I'm reviewing this patch series, and let me give some initial comments and questions. I'm looking at this with a hope that this will be useful purely as a FDW enhancement for our new use cases, regardless of whether the FDW will be used for Postgres scale-out.

I don't think it's necessarily required to combine 2PC with the global visibility. X/Open XA specification only handles the atomic commit. The only part in the XA specification that refers to global visibility is the following:

[Quote from XA specification]
--------------------------------------------------
2.3.2 Protocol Optimisations
・ Read-only
An RM can respond to the TM’s prepare request by asserting that the RM was not
asked to update shared resources in this transaction branch. This response
concludes the RM’s involvement in the transaction; the Phase 2 dialogue between
the TM and this RM does not occur. The TM need not stably record, in its list of
participating RMs, an RM that asserts a read-only role in the global transaction.

However, if the RM returns the read-only optimisation before all work on the global
transaction is prepared, global serialisability1 cannot be guaranteed. This is because
the RM may release transaction context, such as read locks, before all application
activity for that global transaction is finished.

1.
Serialisability is a property of a set of concurrent transactions. For a serialisable set of transactions, at least one
serial sequence of the transactions exists that produces identical results, with respect to shared resources, as does
concurrent execution of the transaction.
--------------------------------------------------

(1)
Do other popular DBMSs (Oracle, MySQL, etc.) provide concrete functions that can be used for the new FDW commit/rollback/prepare API? I'm asking this to confirm that we really need to provide these functions, not as the transaction callbacks for postgres_fdw.

(2)
How are data modifications tracked in local and remote transactions? 0001 seems to handle local INSERT/DELETE/UPDATE. Especially:

* COPY FROM to local/remote tables/views.

* User-defined function calls that modify data, e.g. SELECT func1() WHERE col = func2()

(3)
Does the 2PC processing always go through the background worker?
Is the group commit effective on the remote server? That is, PREPARE and COMMIT PREPARED issued from multiple remote sessions are written to WAL in batch?

Regards
Takayuki Tsunakawa