Re: Transactions involving multiple postgres foreign servers, take 2 - Mailing list pgsql-hackers

From Masahiko Sawada
Subject Re: Transactions involving multiple postgres foreign servers, take 2
Date
Msg-id CAD21AoDK2-Gpkyyr9Ka6BXQ144c1RzpoqKTUevg1X0O3sz8xkQ@mail.gmail.com
Whole thread Raw
In response to Re: Transactions involving multiple postgres foreign servers, take 2  (Masahiko Sawada <sawada.mshk@gmail.com>)
Responses Re: Transactions involving multiple postgres foreign servers, take 2
List pgsql-hackers
On Wed, Nov 25, 2020 at 9:50 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Since the previous version conflicts with the current HEAD I've
> attached the rebased version patch set.

Rebased the patch set again to the current HEAD.

The discussion of this patch is very long so here is a short summary
of the current state:

It’s still under discussion which approaches are the best for the
distributed transaction commit as a building block of built-in sharing
using foreign data wrappers.

Since we’re considering that we use this feature for built-in
sharding, the design depends on the architecture of built-in sharding.
For example, with the current patch, the PostgreSQL node that received
a COMMIT from the client works as a coordinator and it commits the
transactions using 2PC on all foreign servers involved with the
transaction. This approach would be good with the de-centralized
sharding architecture but not with centralized architecture like the
GTM node of Postgres-XC and Postgres-XL that is a dedicated component
that is responsible for transaction management. Since we don't get a
consensus on the built-in sharding architecture yet, it's still an
open question that this patch's approach is really good as a building
block of the built-in sharding.

On the other hand, this feature is not necessarily dedicated to the
built-in sharding. For example, the distributed transaction commit
through FDW is important also when atomically moving data between two
servers via FDWs. Using a dedicated process or server like GTM could
be an over solution. Having the node that received a COMMIT work as a
coordinator would be better and straight forward.

There is no noticeable TODO in the functionality so far covered by
this patch set. This patchset adds new FDW APIs to support 2PC,
introduces the global transaction manager, and implement those FDW
APIs to postgres_fdw. Also, it has regression tests and documentation.
Transactions on foreign servers involved with the distributed
transaction are committed using 2PC. Committing using 2PC is performed
asynchronously and transparently to the user. Therefore, it doesn’t
guarantee that transactions on the foreign server are also committed
when the client gets an acknowledgment of COMMIT. The patch doesn't
cover synchronous foreign transaction commit via 2PC is not covered by
this patch as we still need a discussion on the design.

Regards,

--
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/

Attachment

pgsql-hackers by date:

Previous
From: Arne Roland
Date:
Subject: Re: a misbehavior of partition row movement (?)
Next
From: Tom Lane
Date:
Subject: Re: pgsql: Fix memory leak in plpgsql's CALL processing.