Re: Transactions involving multiple postgres foreign servers, take 2

On Mon, 12 Oct 2020 at 11:08, 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 also doubt how useful the per-foreign-server timeout setting you
> > mentioned before. For example, suppose the transaction involves with
> > three foreign servers that have different timeout setting, what if the
> > backend failed to commit on the first one of the server due to
> > timeout? Does it attempt to commit on the other two servers? Or does
> > it give up and return the control to the client? In the former case,
> > what if the backend failed again on one of the other two servers due
> > to timeout? The backend might end up waiting for all timeouts and in
> > practice the user is not aware of how many servers are involved with
> > the transaction, for example in a sharding. So It seems to be hard to
> > predict the total timeout. In the latter case, the backend might
> > succeed to commit on the other two nodes. Also, the timeout setting of
> > the first foreign server virtually is used as the whole foreign
> > transaction resolution timeout. However, the user cannot control the
> > order of resolution. So again it seems to be hard for the user to
> > predict the timeout. So If we have a timeout mechanism, I think it's
> > better if the user can control the timeout for each transaction.
> > Probably the same is true for the retry.
>
> I agree that the user can control the timeout per transaction, not per FDW. I was just not sure if the Postgres core can define the timeout parameter and the FDWs can follow its setting. However, JTA defines a transaction timeout API (not commit timeout, though), and each RM can choose to implement them. So I think we can define the parameter and/or routines for the timeout in core likewise.

I was thinking to have a GUC timeout parameter like statement_timeout.
The backend waits for the setting value when resolving foreign
transactions. But this idea seems different. FDW can set its timeout
via a transaction timeout API, is that right? 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?

>
>
> --------------------------------------------------
> public interface javax.transaction.xa.XAResource
>
> int getTransactionTimeout() throws XAException
> This method returns the transaction timeout value set for this XAResourceinstance. If XAResource.
> setTransactionTimeout was not use prior to invoking this method, the return value is the
> default timeout set for the resource manager; otherwise, the value used in the previous setTransactionTimeoutcall
> is returned.
>
> Throws: XAException
> An error has occurred. Possible exception values are: XAER_RMERR, XAER_RMFAIL.
>
> Returns:
> The transaction timeout values in seconds.
>
> boolean setTransactionTimeout(int seconds) throws XAException
> This method sets the transaction timeout value for this XAResourceinstance. Once set, this timeout value
> is effective until setTransactionTimeoutis invoked again with a different value. To reset the timeout
> value to the default value used by the resource manager, set the value to zero.
>
> If the timeout operation is performed successfully, the method returns true; otherwise false. If a resource
> manager does not support transaction timeout value to be set explicitly, this method returns false.
>
> Parameters:
>
> seconds
> An positive integer specifying the timout value in seconds. Zero resets the transaction timeout
> value to the default one used by the resource manager. A negative value results in XAException
> to be thrown with XAER_INVAL error code.
>
> Returns:
> true if transaction timeout value is set successfully; otherwise false.
>
> Throws: XAException
> An error has occurred. Possible exception values are: XAER_RMERR, XAER_RMFAIL, or
> XAER_INVAL.
> --------------------------------------------------
>
>
>
> > For example in postgres_fdw, it executes a SQL in asynchronous manner
> > using by PQsendQuery(), PQconsumeInput() and PQgetResult() and so on
> > (see do_sql_command() and pgfdw_get_result()). Therefore it the user
> > pressed ctl-C, the remote query would be canceled and raise an ERROR.
>
> Yeah, as I replied to Horiguchi-san, postgres_fdw can cancel queries. But postgres_fdw is not ready to cancel connection establishment, is it? At present, the user needs to set connect_timeout parameter on the foreign server to a reasonable short time so that it can respond quickly to cancellation requests. Alternately, we can modify postgres_fdw to use libpq's asynchronous connect functions.

Yes, I think using asynchronous connect functions seems a good idea.

> Another issue is that the Postgres manual does not stipulate anything about cancellation of FDW processing. That's why I said that the current FDW does not support cancellation in general. Of course, I think we can stipulate the ability to cancel processing in the FDW interface.

Yeah, it's the FDW developer responsibility to write the code to
execute the remote SQL that is interruptible. +1 for adding that to
the doc.

Regards,

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