Thread: setting query timeout as part of the query

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

Steve Crawford <scrawford@pinpointresearch.com> writes:
> On 09/30/2014 12:52 PM, William Morgan wrote:
>> My question is: is there a way to set this timeout as part of the
>> query itself?

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

IIRC, statement_timeout applies to the total time required to execute
a *command submitted by the client*, and the active value is determined
just before starting each command.  So you're pretty much stuck: you
must issue the SET as a separate client-submitted command.

As Steve says, it should be possible to ensure that the pooler doesn't
take the session away from you in between, by wrapping the whole thing
in BEGIN/COMMIT if nothing else.  If the pooler will take away a session
intra-transaction, it is 100% broken and dangerous, and you need to get
another one.

            regards, tom lane

William Morgan <william@introhq.com> writes:
> If I understand you both correctly, wrapping every statement in a
> transaction will have no appreciable difference in speed or level of
> concurrency, since that wrapping would otherwise already happen under the
> hood. Since the Go pooler respects transactions, that seems like the answer
> to me.

Right.

> And if the execution of the set command itself turns out to be costly
> (though that sounds unlikely) there is a backup plan involving a series of
> user accounts with different timeouts that will at least allow me to
> approximate the limits that I want.

It shouldn't be terribly costly.  One thing you might consider, if you're
wrapping the statements in transactions anyway, is to use SET LOCAL whose
effects won't last beyond the transaction.  I don't think this will make
any notable difference speed-wise, but it just seems a tad cleaner if the
timeouts don't persist.

            regards, tom lane

On Tue, Sep 30, 2014 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Steve Crawford <scrawford@pinpointresearch.com> writes:
>> On 09/30/2014 12:52 PM, William Morgan wrote:
>>> My question is: is there a way to set this timeout as part of the
>>> query itself?
>
>> 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.
>
> IIRC, statement_timeout applies to the total time required to execute
> a *command submitted by the client*, and the active value is determined
> just before starting each command.  So you're pretty much stuck: you
> must issue the SET as a separate client-submitted command.

It sure would be nice to be able to tuck this stuff into the database.
On my team database and application side development are fairly
segregated and I prefer not to delegate such things to the application
developers.  Other stuff in this vein that you can't do server-side:

* transaction mangement
* control of isolation level
* locks to prevent serialization errors

merlin