sql: query timeout determination is inconsistent with PG when portals are involved #99140
Description
I ran the following tests against cockroach and postgres, and the timeout seems to happen at different timestamps.
So we have a portal whose each execution lasts for 0.5 second. We set the statement timeout to be 2s. For PG, the timeout error shows up at the 5th execution, but cockroach gives the timeout at the 4th execution.
subtest query_timeout
send
Query {"String": "BEGIN"}
Query {"String": "SET statement_timeout='2s'"}
Parse {"Name": "q20", "Query": "SELECT i, pg_sleep(0.5) FROM generate_series(1, 6) AS g(i)"}
Bind {"DestinationPortal": "p20", "PreparedStatement": "q20"}
Execute {"Portal": "p20", "MaxRows": 1}
Execute {"Portal": "p20", "MaxRows": 1}
Execute {"Portal": "p20", "MaxRows": 1}
Execute {"Portal": "p20", "MaxRows": 1}
Execute {"Portal": "p20", "MaxRows": 1}
Sync
----
# The output for pg_sleep differ between cockroach and postgres:
# https://github.com/cockroachdb/cockroach/issues/98913
until crdb_only keepErrMessage
ReadyForQuery
ReadyForQuery
ErrorResponse
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"BEGIN"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"SET"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"DataRow","Values":[{"text":"1"},{"text":"t"}]}
{"Type":"PortalSuspended"}
{"Type":"DataRow","Values":[{"text":"2"},{"text":"t"}]}
{"Type":"PortalSuspended"}
{"Type":"DataRow","Values":[{"text":"3"},{"text":"t"}]}
{"Type":"PortalSuspended"}
{"Type":"ErrorResponse","Code":"57014","Message":"query execution canceled due to statement timeout"}
{"Type":"ReadyForQuery","TxStatus":"E"}
until noncrdb_only keepErrMessage
ReadyForQuery
ReadyForQuery
ErrorResponse
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"BEGIN"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"CommandComplete","CommandTag":"SET"}
{"Type":"ReadyForQuery","TxStatus":"T"}
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"DataRow","Values":[{"text":"1"},null]}
{"Type":"PortalSuspended"}
{"Type":"DataRow","Values":[{"text":"2"},null]}
{"Type":"PortalSuspended"}
{"Type":"DataRow","Values":[{"text":"3"},null]}
{"Type":"PortalSuspended"}
{"Type":"DataRow","Values":[{"text":"4"},null]}
{"Type":"PortalSuspended"}
{"Type":"ErrorResponse","Code":"57014","Message":"canceling statement due to statement timeout"}
{"Type":"ReadyForQuery","TxStatus":"E"}
Jira issue: CRDB-25723
Epic CRDB-25183