Re: setting query timeout as part of the query

On 09/30/2014 12:52 PM, William Morgan wrote:
> 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