Re: Transactions involving multiple postgres foreign servers, take 2

On Mon, 12 Oct 2020 at 17:19, tsunakawa.takay@fujitsu.com
<tsunakawa.takay@fujitsu.com> wrote:
>
> From: Masahiko Sawada <masahiko.sawada@2ndquadrant.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
parameterfor transaction timeout, and the TM calls XAResource.setTransactionTimeout() with that or smaller value for
theargument. 
>
>
> > 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
findsthat 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