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
thatthis will be useful purely as a FDW enhancement for our new use cases, regardless of whether the FDW will be used
forPostgres scale-out.
 

I don't think it's necessarily required to combine 2PC with the global visibility.  X/Open XA specification only
handlesthe 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/prepareAPI?  I'm asking this to confirm that we really need to provide these functions, not as the
transactioncallbacks 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
sessionsare written to WAL in batch?
 


Regards
Takayuki Tsunakawa