Re: Transactions involving multiple postgres foreign servers

>>
>> 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.

>
> 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.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company