RE: Transactions involving multiple postgres foreign servers, take 2

From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> On Fri, 25 Sep 2020 at 18:21, tsunakawa(dot)takay(at)fujitsu(dot)com
> <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > Why does the client backend have to create a new connection cache entry
> during PREPARE or COMMIT PREPARE? Doesn't the client backend naturally
> continue to use connections that it has used in its current transaction?
>
> I think there are two cases: a process executes PREPARE TRANSACTION
> and another process executes COMMIT PREPARED later, and if the
> coordinator has cascaded foreign servers (i.g., a foreign server has
> its foreign server) and temporary connection problem happens in the
> intermediate node after PREPARE then another process on the
> intermediate node will execute COMMIT PREPARED on its foreign server.

Aren't both the cases failure cases, and thus handled by the resolver?

> > > In terms of performance you're concerned, I wonder if we can somewhat
> > > eliminate the bottleneck if multiple resolvers are able to run on one
> > > database in the future. For example, if we could launch resolver
> > > processes as many as connections on the database, individual backend
> > > processes could have one resolver process. Since there would be
> > > contention and inter-process communication it still brings some
> > > overhead but it might be negligible comparing to network round trip.
> >
> > Do you mean that if concurrent 200 clients each update data on two foreign
> servers, there are 400 resolvers? ...That's overuse of resources.
>
> I think we have 200 resolvers in this case since one resolver process
> per backend process.

That does not parallelize prepare or commit for a single client, as each resolver can process only one prepare or commit synchronously at a time. Not to mention the resource usage is high.

> Or another idea is that all processes queue
> foreign transactions to resolve into the shared memory queue and
> resolver processes fetch and resolve them instead of assigning one
> distributed transaction to one resolver process. Using asynchronous
> execution, the resolver process can process a bunch of foreign
> transactions across distributed transactions and grouped by the
> foreign server at once. It might be more complex than the current
> approach but having multiple resolver processes on one database would
> increase through-put well especially by combining with asynchronous
> execution.

Yeah, that sounds complex. It's simpler and natural for each client backend to use the connections it has used in its current transaction and issue prepare and commit to the foreign servers, and the resolver just takes care of failed commits and aborts behind the scenes. That's like the walwriter takes care of writing WAL based on the client backend that commits asynchronously.

Regards
Takayuki Tsunakawa