Re: setting query timeout as part of the query - Mailing list pgsql-novice

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;

select * from users;

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.)

What pooling are you using and what settings does it allow? If you are using pgbouncer you can set the pooler to hold a database connection for the duration of a client connection, for the duration of a transaction or for the duration of a statement. If connection-level pooling can work for you then you don't have to worry about the client switching backends. Whatever pooler you are using, make sure that you don't have settings from one connection be they timeouts or other settings like locale, timezone, memory-settings, etc. unexpectedly impact a subsequent connection.

Although "set..." is not an expensive statement it is possible, though rare and unlikely, to have the set statement exceed the desired time. We set timeouts for certain statements and haven't had trouble with set using excessive time. There are numerous pooling options for PostgreSQL and I haven't examined them all but if the pooler were to queue client connections until a server connection becomes available your attempt to enforce timeouts at the server-side could be futile.

Wrapping things into a transaction to force the pooler to stick with a single transaction isn't the worst option - remember that every stand-alone statement is automatically wrapped into a one-statement transaction anyway so you aren't actually increasing the number of transactions.

You could probably write a function that includes your timeout setting and call that but I'm not sure that would be better than simply using a transaction.

In some cases you can create a new user with the defaults, including timeout, that you desire and simply run specific queries as that user.

Cheers,
Steve