Re: Transactions involving multiple postgres foreign servers, take 2

On 2020-09-03 23:08, Masahiko Sawada wrote:
> On Fri, 28 Aug 2020 at 17:50, Masahiro Ikeda <ikedamsh(at)oss(dot)nttdata(dot)com>
> wrote:
>>
>> > I think there is a case we can't check orphaned foreign
>> > prepared transaction in pg_foreign_xacts view on the new standby
>> > server.
>> > It confuses users and database administrators.
>> >
>> > If the primary coordinator crashes after preparing foreign transaction,
>> > but before sending XLOG_FDWXACT_INSERT records to the standby server,
>> > the standby server can't restore their transaction status and
>> > pg_foreign_xacts view doesn't show the prepared foreign transactions.
>> >
>> > To send XLOG_FDWXACT_INSERT records asynchronously leads this problem.
>>
>> If the primary replicates XLOG_FDWXACT_INSERT to the standby
>> asynchronously,
>> some prepared transaction may be unsolved forever.
>>
>> Since I think to solve this inconsistency manually is hard operation,
>> we need to support synchronous XLOG_FDWXACT_INSERT replication.
>>
>> I understood that there are a lot of impact to the performance,
>> but users can control the consistency/durability vs performance
>> with synchronous_commit parameter.
>>
>> What do you think?
>
> I think the user can check such prepared transactions by seeing
> transactions that exist on the foreign server's pg_prepared_xact but
> not on the coordinator server's pg_foreign_xacts, no? To make checking
> such prepared transactions easy, perhaps we could contain the
> timestamp to prepared transaction id. But I’m concerned the
> duplication of transaction id due to clock skew.

Thanks for letting me know.
I agreed that we can check pg_prepared_xact and pg_foreign_xacts.

We have to abort the transaction which exists in pg_prepared_xact and
doesn't exist in pg_foreign_xacts manually, don't we?
So users have to use the foreign database which supports to show
prepared transaction status like pg_foreign_xacts.

When duplication of transaction id is made?
I'm sorry that I couldn't understand about clock skew.

IICU, since prepared id may have coordinator's xid, there is no clock
skew
and we can determine transaction_id uniquely.
If the fdw implements GetPrepareId_function API and it generates
transaction_id without coordinator's xid, your concern will emerge.
But, I can't understand the case to generate transaction_id without
coordinator's xid.

> If there is a way to identify such unresolved foreign transactions and
> it's not cumbersome, given that the likelihood of problem you're
> concerned is unlikely high I guess a certain number of would be able
> to accept it as a restriction. So I’d recommend not dealing with this
> problem in the first version patch and we will be able to improve this
> feature to deal with this problem as an additional feature. Thoughts?

I agree. Thanks for your comments.

>> > Thank you for letting me know. I've attached the latest version patch
>> > set.
>>
>> Thanks for updating.
>> But, the latest patches failed to be applied to the master branch.
>
> I'll submit the updated version patch.

Thanks.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION