On Mon, 12 Oct 2020 at 11:08, tsunakawa.ta...@fujitsu.com <tsunakawa.ta...@fujitsu.com> wrote: > > From: Masahiko Sawada <masahiko.saw...@2ndquadrant.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
