Re: [HACKERS] Transactions involving multiple postgres foreign servers

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.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company