Re: Transactions involving multiple postgres foreign servers

On Wed, Sep 27, 2017 at 11:15 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> I think that making a resolver process have connection caches to each
> foreign server for a while can reduce the overhead of connection to
> foreign servers. These connections will be invalidated by DDLs. Also,
> most of the time we spend to commit a distributed transaction is the
> interaction between the coordinator and foreign servers using
> two-phase commit protocal. So I guess the time in signalling to a
> resolver process would not be a big overhead.

I agree. Also, in the future, we might try to allow connections to be
shared across backends. I did some research on this a number of years
ago and found that every operating system I investigated had some way
of passing a file descriptor from one process to another -- so a
shared connection cache might be possible.

Also, we might port the whole backend to use threads, and then this
problem goes way. But I don't have time to write that patch this
week. :-)

It's possible that we might find that neither of the above approaches
are practical and that the performance benefits of resolving the
transaction from the original connection are large enough that we want
to try to make it work anyhow. However, I think we can postpone that
work to a future time. Any general solution to this problem at least
needs to be ABLE to resolve transactions at a later time from a
different session, so let's get that working first, and then see what
else we want to do.

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