Re: Transactions involving multiple postgres foreign servers, take 2

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.

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?

> > 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.

Regards,
--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services