Re: Transactions involving multiple postgres foreign servers

On 06.11.2015 21:37, Robert Haas wrote:
> On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> The previous patch would not compile on the latest HEAD. Here's updated
>> patch.
> Perhaps unsurprisingly, this doesn't apply any more.  But we have
> bigger things to worry about.
>
> The recent eXtensible Transaction Manager and the slides shared at the
> Vienna sharding summit, now posted at
> https://drive.google.com/file/d/0B8hhdhUVwRHyMXpRRHRSLWFXeXc/view make
> me think that some careful thought is needed here about what we want
> and how it should work. Slide 10 proposes a method for the extensible
> transaction manager API to interact with FDWs.  The FDW would do this:
>
> select dtm_join_transaction(xid);
> begin transaction;
> update...;
> commit;
>
> I think the idea here is that the commit command doesn't really
> commit; it just escapes the distributed transaction while leaving it
> marked not-committed.  When the transaction subsequently commits on
> the local server, the XID is marked committed and the effects of the
> transaction become visible on all nodes.
>
> I think that this API is intended to provide not only consistent
> cross-node decisions about whether a particular transaction has
> committed, but also consistent visibility.  If the API is sufficient
> for that and if it can be made sufficiently performant, that's a
> strictly stronger guarantee than what this proposal would provide.
>
> On the other hand, I see a couple of problems:
>
> 1. The extensible transaction manager API is meant to be pluggable.
> Depending on which XTM module you choose to load, the SQL that needs
> to be executed by postgres_fdw on the remote node will vary.
> postgres_fdw shouldn't have knowledge of all the possible XTMs out
> there, so it would need some way to know what SQL to send.
>
> 2. If the remote server isn't running the same XTM as the local
> server, or if it is running the same XTM but is not part of the same
> group of cooperating nodes as the local server, then we can't send a
> command to join the distributed transaction at all.  In that case, the
> 2PC for FDW approach is still, maybe, useful.
>
> On the whole, I'm inclined to think that the XTM-based approach is
> probably more useful and more general, if we can work out the problems
> with it.  I'm not sure that I'm right, though, nor am I sure how hard
> it will be.
Sorry, but we currently considered only case of homogeneous environment: 
when all cluster instances are using PostgreSQL with the same XTM 
implementation.
I can imagine situations when it may be useful to coordinate transaction 
processing in heterogeneous cluster, but it seems to be quite exotic use 
case.
Combining several different databases on one cluster can be explained by 
some historical reasons or specific of particular system architecture. 
But I can not imagine any reason for using different XTM implementations 
and especially mixing them in one transaction.