RE: Transactions involving multiple postgres foreign servers, take 2

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.

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

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.

Regards
Takayuki Tsunakawa