Re: [HACKERS] Transactions involving multiple postgres foreign servers, take 2

On Tue, Jun 5, 2018 at 7:13 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Sat, May 26, 2018 at 12:25 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, May 18, 2018 at 11:21 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>> 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.
>>
>> It seems to me that this is functionality that many people will not
>> want to use. First, doing a PREPARE and then a COMMIT for each FDW
>> write transaction is bound to be more expensive than just doing a
>> COMMIT. Second, because the default value of
>> max_prepared_transactions is 0, this can only work at all if special
>> configuration has been done on the remote side. Because of the second
>> point in particular, it seems to me that the default for this new
>> feature must be "off". It would make to ship a default configuration
>> of PostgreSQL that doesn't work with the default configuration of
>> postgres_fdw, and I do not think we want to change the default value
>> of max_prepared_transactions. It was changed from 5 to 0 a number of
>> years back for good reason.
>
> I'm not sure that many people will not want to use this feature
> because it seems to me that there are many people who don't want to
> use the database that is missing transaction atomicity. But I agree
> that this feature should not be enabled by default as we disable 2PC
> by default.
>
>>
>> So, I think the question could be broadened a bit: how you enable this
>> feature if you want it, and what happens if you want it but it's not
>> available for your choice of FDW? One possible enabling method is a
>> GUC (e.g. foreign_twophase_commit). It could be true/false, with true
>> meaning use PREPARE for all FDW writes and fail if that's not
>> supported, or it could be three-valued, like require/prefer/disable,
>> with require throwing an error if PREPARE support is not available and
>> prefer using PREPARE where available but without failing when it isn't
>> available. Another possibility could be to make it an FDW option,
>> possibly capable of being set at multiple levels (e.g. server or
>> foreign table). If any FDW involved in the transaction demands
>> distributed 2PC semantics then the whole transaction must have those
>> semantics or it fails. I was previous leaning toward the latter
>> approach, but I guess now the former approach is sounding better. I'm
>> not totally certain I know what's best here.
>>
>
> I agree that the former is better. That way, we also can control that
> parameter at transaction level. If we allow the 'prefer' behavior we
> need to manage not only 2PC-capable foreign server but also
> 2PC-non-capable foreign server. It requires all FDW to call the
> registration function. So I think two-values parameter would be
> better.
>
> BTW, sorry for late submitting the updated patch. I'll post the
> updated patch in this week but I'd like to share the new APIs design
> beforehand.

Attached updated patches.

I've changed the new APIs to 5 functions and 1 registration function
because the rollback API can be called by both backend process and
resolver process which is not good design. The latest version patches
incorporated all comments I got except for documentation about overall
point to user. I'm considering what contents I should document it
there. I'll write it during the code patch is getting reviewed. The
basic design of new patches is almost same as the previous mail I
sent.

I introduced 5 new FDW APIs: PrepareForeignTransaction,
CommitForeignTransaction, RollbackForeignTransaction,
ResolveForeignTransaction and IsTwophaseCommitEnabled.
ResolveForeignTransaction is normally called by resolver process
whereas other four functions are called by backend process. Also I
introduced a registration function FdwXactRegisterForeignTransaction.
FDW that wish to support atomic commit requires to call this function
when a transaction opens on the foreign server. Registered foreign
transactions are controlled by the foreign transaction manager of
Postgres core and calls APIs at appropriate timing. It means that the
foreign transaction manager controls only foreign servers that are
capable of 2PC. For 2PC-non-capable foreign server, FDW must use
XactCallback to control the foreign transaction. 2PC is used at commit
when the distributed transaction modified data on two or more servers
including local server and user requested by foreign_twophase_commit
GUC parameter. All foreign transactions are prepared during pre-commit
and then commit locally. After committed locally wait for resolver
process to resolve all prepared foreign transactions. The waiting
backend is released (that is, returns the prompt to client) either
when all foreign transactions are resolved or when user requested to
waiting. If 2PC is not required, a foreign transaction is committed
during pre-commit phase of local transaction. IsTwophaseCommitEnabled
is called whenever the transaction begins to modify data on foreign
server. This is required to track whether the transaction modified
data on the foreign server that doesn't support or enable 2PC.

Atomic commit among multiple foreign servers is crash-safe. If the
coordinator server crashes during atomic commit, the foreign
transaction participants and their status are recovered during WAL
apply. Recovered foreign transactions are in doubt-state, aka dangling
transactions. If database has such transactions resolver process
periodically tries to resolve them.

I'll register this patch to next CF. Feedback is very welcome.

Regards,

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