Re: Transactions involving multiple postgres foreign servers

On Mon, Oct 2, 2017 at 3:31 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Sat, Sep 30, 2017 at 12:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> 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.
>
> It sounds good idea.
>
>> 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.
>>
>
> I understood and agreed. I'll post the first version patch of new
> design to next CF.
>

Attached latest version patch. I've heavily changed the patch since
previous one. The most part I modified is the resolving foreign
transaction and handling of dangling transactions. The part of
management of fdwxact entries is almost same as the previous patch.

Foreign Transaction Resolver
======================
I introduced a new background worker called "foreign transaction
resolver" which is responsible for resolving the transaction prepared
on foreign servers. The foreign transaction resolver process is
launched by backend processes when commit/rollback transaction. And it
periodically resolves the queued transactions on a database as long as
the queue is not empty. If the queue has been empty for the certain
time specified by foreign_transaction_resolver_time GUC parameter, it
exits. It means that the backend doesn't launch a new resolver process
if the resolver process is already working. In this case, the backend
process just adds the entry to the queue on shared memory and wake it
up. The maximum number of resolver process we can launch is controlled
by max_foreign_transaction_resolvers. So we recommends to set larger
max_foreign_transaction_resolvers value than the number of databases.
The resolver process also tries to resolve dangling transaction as
well in a cycle.

Processing Sequence
=================
I've changed the processing sequence of resolving foreign transaction
so that the second phase of two-phase commit protocol (COMMIT/ROLLBACK
prepared) is executed by a resolver process, not by backend process.
The basic processing sequence is following;

* Backend process
1. In pre-commit phase, the backend process saves fdwxact entries, and
then prepares transaction on all foreign servers that can execute
two-phase commit protocol.
2. Local commit.
3. Enqueue itself to the shmem queue and change its status to WAITING
4. launch or wakeup a resolver process and wait

* Resolver process
1. Dequeue the waiting process from shmem qeue
2. Collect the fdwxact entries that are associated with the waiting process.
3. Resolve foreign transactoins
4. Release the waiting process

5. Wake up and restart

This is still under the design phase and I'm sure that there is room
for improvement and consider more sensitive behaviour but I'd like to
share the current status of the patch. The patch includes regression
tests but not includes fully documentation.

Feedback and comment are very welcome.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center