RE: [HACKERS] Transactions involving multiple postgres foreignservers - Mailing list pgsql-hackers

From: Masahiko Sawada [mailto:sawada.mshk@gmail.com]
> Regarding to API design, should we use 2PC for a distributed
> transaction if both two or more 2PC-capable foreign servers and
> 2PC-non-capable foreign server are involved with it?  Or should we end
> up with an error? the 2PC-non-capable server might be either that has
> 2PC functionality but just disables it or that doesn't have it.

>but I think we also could take
> the latter way because it doesn't make sense for user even if the
> transaction commit atomically among not all participants.

I'm for the latter.  That is, COMMIT or PREPARE TRANSACTION statement issued from an application reports an error.
DBMS,particularly relational DBMS (, and even more particularly Postgres?) places high value on data correctness.  So I
thinktransaction atomicity should be preserved, at least by default.  If we preferred updatability and performance to
datacorrectness, why don't we change the default value of synchronous_commit to off in favor of performance?  On the
otherhand, if we want to allow 1PC commit when not all FDWs support 2PC, we can add a new GUC parameter like
"allow_nonatomic_commit= on", just like synchronous_commit and fsync trade-offs data correctness and performance.
 


> Also, regardless whether we take either way  I think it would be
> better to manage not only 2PC transaction but also non-2PC transaction
> in the core and add two_phase_commit argument. I think we can use it
> without breaking existing FDWs. Currently FDWs manage transactions
> using XactCallback but new APIs being added also manage transactions.
> I think it might be better if users use either way (using XactCallback
> or using new APIs) for transaction management rather than use both
> ways with combination. Otherwise two codes for transaction management
> will be required: the code that manages foreign transactions using
> XactCallback for non-2PC transactions and code that manages them using
> new APIs for 2PC transactions. That would not be easy for FDW
> developers. So what I imagined for new API is that if FDW developers
> use new APIs they can use both 2PC and non-2PC transaction, but if
> they use XactCallback they can use only non-2PC transaction.
> Any thoughts?

If we add new functions, can't we just add functions whose names are straightforward like PrepareTransaction() and
CommitTransaction()? FDWs without 2PC support returns NULL for the function pointer of PrepareTransaction().
 

This is similar to XA: XA requires each RM to provide function pointers for xa_prepare() and xa_commit().  If we go
thisway, maybe we could leverage the artifact of postgres_fdw to create the XA library for C/C++.  I mean we put
transactioncontrol functions in the XA library, and postgres_fdw also uses it.  i.e.:
 

    postgres_fdw.so -> libxa.so -> libpq.so
                     \-------------/


Regards
Takayuki Tsunakawa