Dear Postgres experts,
Hello! Postgres (but not SQL) novice here.
I'm building a system which has per-query, per-execution deadlines. For example, for a particular invocation of a query like "select * from users", I'd like to ensure that this operation takes no more than 3 seconds; for another invocation of that query, I'd like to ensure that it takes no more than 2300ms. (These deadlines come from the broader execution context in which these queries are performed.)
So far, so good. I'm aware of the statement_timeout configuration variable that allows you to set execution timeouts, so I could do something like:
set session statement_timeout to 3000;
set session statement_timeout to 2300;
select * from users;
My question is: is there a way to set this timeout as part of the query itself? I ask because I'm in a situation where, due to connection pooling on the driver side, I cannot guarantee that I can actually execute two consecutive commands like the above on the same session.
I've played around with various ways of inserting set_config() into queries (e.g. within subselects), but haven't found a combination that seems affect the current query. Maybe I missed some clever way to do this?
(I could also wrap everything within a transaction, but that seems like an expensive way to accomplish this.)
Thanks in advance for any help.
-William