Re: Transactions involving multiple postgres foreign servers, take 2

On 2021/10/07 19:47, Etsuro Fujita wrote:
> Hi,
>
> On Thu, Oct 7, 2021 at 1:29 PM k(dot)jamison(at)fujitsu(dot)com
> <k(dot)jamison(at)fujitsu(dot)com> wrote:
>> That said, if we're going to initially support it on postgres_fdw, which is simpler
>> than the latest patches, we need to ensure that abnormalities and errors
>> are properly handled

Yes. One idea for this is to include the information required to resolve
outstanding prepared transactions, in the transaction identifier that
PREPARE TRANSACTION command uses. For example, we can use the XID of
local transaction and the cluster ID of local server (e.g., cluster_name
that users specify uniquely can be used for that) as that information.
If the cluster_name of local server is "server1" and its XID is now 9999,
postgres_fdw issues "PREPARE TRANSACTION 'server1_9999'" and
"COMMIT PREPARED 'server1_9999'" to the foreign servers, to end those
foreign transactions in two-phase way.

If some troubles happen, the prepared transaction with "server1_9999"
may remain unexpectedly in one foreign server. In this case we can
determine whether to commit or rollback that outstanding transaction
by checking whether the past transaction with XID 9999 was committed
or rollbacked in the server "server1". If it's committed, the prepared
transaction also should be committed, so we should execute
"COMMIT PREPARED 'server1_9999'". If it's rollbacked, the prepared
transaction also should be rollbacked. If it's in progress, we should
do nothing for that transaction.

pg_xact_status() can be used to check whether the transaction with
the specified XID was committed or rollbacked. But pg_xact_status()
can return invalid result if CLOG data for the specified XID has been
truncated by VACUUM FREEZE. To handle this case, we might need
the special table tracking the transaction status.

DBA can use the above procedure and manually resolve the outstanding
prepared transactions in foreign servers. Also probably we can implement
the function doing the procedure. If so, it might be good idea to make
background worker or cron periodically execute the function.

>> and prove that commit performance can be improved,
>> e.g. if we can commit not in serial but also possible in parallel.
>
> If it's ok with you, I'd like to work on the performance issue. What
> I have in mind is commit all remote transactions in parallel instead
> of sequentially in the postgres_fdw transaction callback, as mentioned
> above, but I think that would improve the performance even for
> one-phase commit that we already have.

+100

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION