How to set query timeout?

[ysn2233]

Is query timeout supported like JDBC driver?

[abonander]

You can time-out a query using tokio::time::timeout or async_std::future::timeout but that will only cancel the wait for the query response on the client-side. The server may still be left in a busy state calculating the query results, so the next query on the connection will have to wait for the server to finish the previous one.

Additionally, cancelling an in-flight query might break the connection as we have issues with cancellation still. Postgres and SQLite should be able to handle cancellation okay, but MySQL doesn't (#563). If you're using Pool it will check if the connection is broken and close it so it doesn't get used again.

All that said, I'd recommend setting a timeout on the server side.

With Postgres, you can set statement_timeout via conn.execute("SET statement_timeout = <milliseconds>").await? before a long query, or if you want it to apply globally you can set it on PgConnectOptions.

MySQL provides max_execution_time, although it explicitly only applies to SELECTs.

[airekans]

Hi, abonander

Does sqlx support query canceling in postgres?
I am currently also exploring the possibility to set query timeout with sqlx. Even though I can cancel the wait from client side, I also want to cancel the query running to free the resource from the server side.
Postgres does support query canceling with CancelRequest, but AKAIK, currently sqlx for postgres does not use the process_id/secret_key for cancel request, which means sqlx for postgres does not support cancelling in-progress query natively.
I could explore other alternatives, e.g. the statement_timeout approach you mentioned, but just want to confirm that's what we have for sqlx currently.