Transactions involving multiple postgres foreign servers

On Tue, Oct 4, 2016 at 1:26 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com <javascript:;>> wrote:
>>>
>>> Why always rollback any dangling transaction? There can be a case that
>>> a foreign server has a dangling transaction which needs to be
>>> committed because the portions of that transaction on the other shards
>>> are committed.
>>
>> Right, we can heuristically make a decision whether we do COMMIT or
>> ABORT on local server.
>> For example, if COMMIT PREPARED succeeded on at least one foreign
>> server, the local server return OK to client and the other dangling
>> transactions should be committed later.
>> We can find out that we should do either commit or abort the dangling
>> transaction by checking CLOG.
>
> Heuristics can not become the default behavior. A user should be given
> an option to choose a heuristic, and he should be aware of the
> pitfalls when using this heuristic. I guess, first, we need to get a
> solution which ensures that the transaction gets committed on all the
> servers or is rolled back on all the foreign servers involved. AFAIR,
> my patch did that. Once we have that kind of solution, we can think
> about heuristics.

I meant that we could determine it heuristically only when remote server
crashed in 2nd phase of 2PC.
For example, what does the local server returns to client when no one
remote server returns OK to local server in 2nd phase of 2PC for more than
statement_timeout seconds? Ok or error?

>>
>> But we need to handle the case where the CLOG file containing XID
>> necessary for resolving dangling transaction is truncated.
>> If the user does VACUUM FREEZE just after remote server crashed, it
>> could be truncated.
>
> Hmm, this needs to be fixed. Even my patch relied on XID to determine
> whether the transaction committed or rolled back locally and thus to
> decide whether it should be committed or rolled back on all the
> foreign servers involved. I think I had taken care of the issue you
> have pointed out here. Can you please verify the same?
>
>>
>>> The way gid is crafted, there is no way to check whether the given
>>> prepared transaction was created by the local server or not. Probably
>>> the local server needs to add a unique signature in GID to identify
>>> the transactions prepared by itself. That signature should be
>>> transferred to standby to cope up with the fail-over of local server.
>>
>> Maybe we can use database system identifier in control file.
>
> may be.
>
>>
>>> In this idea, one has to keep on polling the foreign server to find
>>> any dangling transactions. In usual scenario, we shouldn't have a
>>> large number of dangling transactions, and thus periodic polling might
>>> be a waste.
>>
>> We can optimize it by storing the XID that is resolved heuristically
>> into the control file or system catalog, for example.
>>
>
> There will be many such XIDs. We don't want to dump so many things in
> control file, esp. when that's not control data. System catalog is out
> of question since a rollback of local transaction would make those
> rows in the system catalog invisible. That's the reason, why I chose
> to write the foreign prepared transactions to files rather than a
> system catalog.
>

We can store the lowest in-doubt transaction id (say in-doubt XID) that
needs to be resolved later into control file and the CLOG containing XID
greater than in-doubt XID is never truncated.
We need to try to solve such transaction only when in-doubt XID is not NULL.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

--
Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center