RE: Transactions involving multiple postgres foreign servers, take 2

From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> On Mon, 19 Oct 2020 at 14:39, tsunakawa(dot)takay(at)fujitsu(dot)com
> <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > I have to say that's nitpicking. I believe almost nobody does, or cares about,
> canceling commits,
>
> Really? I don’t think so. I think It’s terrible that the query gets
> stuck for a long time and we cannot do anything than waiting until a
> crashed foreign server is restored. We can have a timeout but I don’t
> think every user wants to use the timeout or the user might want to
> set a timeout to a relatively large value by the concern of
> misdetection. I guess synchronous replication had similar concerns so
> it has a similar mechanism.

Really. I thought we were talking about canceling commits with Ctrl + C as you referred, right? I couldn't imagine, in production environments where many sessions are running transactions concurrently, how the user (DBA) wants and can cancel each stuck session during commit one by one with Ctrl + C by hand. I haven't seen such a feature exist or been considered crucial that enables the user (administrator) to cancel running processing with Ctrl + C from the side.

Rather, setting appropriate timeout is the current sound system design , isn't it? It spans many areas - TCP/IP, heartbeats of load balancers and clustering software, request and response to application servers and database servers, etc. I sympathize with your concern that users may not be confident about their settings. But that's the current practice unfortunately.

> > at the expense of impractical performance due to non-parallelism, serial
> execution in each resolver, and context switches.
>
> I have never said that we’re going to live with serial execution in
> each resolver and non-parallelism. I've been repeatedly saying that it
> would be possible that we improve this feature over the releases to
> get a good performance even if we use a separate background process.

IIRC, I haven't seen a reasonable design based on a separate process that handles commits during normal operation. What I heard is to launch as many resolvers as the client sessions, but that consumes too much resource as I said.

> Using a background process to commit is the only option to support
> interruptible foreign transaction resolution for now whereas there are
> some ideas for performance improvements.

A practical solution is the timeout for the FDW in general, as in application servers. postgres_fdw can benefit from Ctrl + C as well.

> I think we don't have enough
> discussion on how we can improve the idea of using a separate process
> and how much performance will improve and how possible it is. It's not
> late to reject that idea after the discussion.

Yeah, I agree that discussion is not enough yet. In other words, the design has not reached the quality for the first release yet. We should try to avoid using "Hopefully, we should be able to improve in the next release (I haven't seen the design in light, though)" as an excuse for getting a half-baked patch committed that does not offer practical quality. I saw many developers' patches were rejected because of insufficient performance, e.g. even 0.8% performance impact. (I'm one of those developers, actually...) I have been feeling this community is rigorous about performance. We have to be sincere.

Regards
Takayuki Tsunakawa