sql: set max statement time on a per query basis

[awoods187]

We now have a statement_timeout and a idle_in_session_timeout (introduced in 20.2). In #5924 we plan to add a idle_in_transaction_session_timeout. We should also add a per-query timeout (instead of a session variable/cluster setting). This is analogous to an INDEX or JOIN hint in that we are instructing specific behavior for an individual query.

In MariaDB, they offer this via max_statement_time

SET STATEMENT max_statement_time=100 FOR 
  SELECT field1 FROM table_name ORDER BY field1;

I couldn't find an example in Postgres (I believe it only allows for the connection or session settings).

Jira issue: CRDB-3972

[jordanlewis]

Thumbs down for making this a priority, if only because no existing Postgres driver will have built-in support for this syntax.

[awoods187]

I understand that some drivers make this an option, for example in JDBC:

Statement stmt = connection.prepareStatement("SELECT * FROM BOOKS");
stmt.setQueryTimeout(10);//Timeout of 10 seconds

But others do not (I believe the Python PG driver only offers it on a connection basis). I think this may end up a critical piece of last-resort intervention analogous to JOIN HINTS and therefore that it still merits further consideration.

[vy-ton]

@rafiss fyi

[rafiss]

The PGJDBC setQueryTimeout method actually is just a client-side timeout. It starts a timer in the client, and after the time is elapsed, it issues a pgwire Cancel message (see #41335).

[jordanlewis]

Okay, so are we good to close this?

[awoods187]

No, I don't think so. We don't have this option available in all drivers.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!