Re: Transactions involving multiple postgres foreign servers, take 2

On Mon, 12 Oct 2020 at 17:19, tsunakawa(dot)takay(at)fujitsu(dot)com
<tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
>
> From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> > I was thinking to have a GUC timeout parameter like statement_timeout.
> > The backend waits for the setting value when resolving foreign
> > transactions.
>
> Me too.
>
>
> > But this idea seems different. FDW can set its timeout
> > via a transaction timeout API, is that right?
>
> I'm not perfectly sure about how the TM( application server works) , but probably no. The TM has a configuration parameter for transaction timeout, and the TM calls XAResource.setTransactionTimeout() with that or smaller value for the argument.
>
>
> > But even if FDW can set
> > the timeout using a transaction timeout API, the problem that client
> > libraries for some DBMS don't support interruptible functions still
> > remains. The user can set a short time to the timeout but it also
> > leads to unnecessary timeouts. Thoughts?
>
> Unfortunately, I'm afraid we can do nothing about it. If the DBMS's client library doesn't support cancellation (e.g. doesn't respond to Ctrl+C or provide a function that cancel processing in pgorogss), then the Postgres user just finds that he can't cancel queries (just like we experienced with odbc_fdw.)

So the idea of using another process to commit prepared foreign
transactions seems better also in terms of this point. Even if a DBMS
client library doesn’t support query cancellation, the transaction
commit can return the control to the client when the user press ctl-c
as the backend process is just sleeping using WaitLatch() (it’s
similar to synchronous replication)

Regards,

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