Re: Statement timeout

On 1 June 2016 at 08:33, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

> > FWIW, I think the existing behavior is just fine. It corresponds to what
> > PQexec has always done with multi-statement query strings; that is,
> > statement_timeout governs the total time to execute the transaction (the
> > whole query string, unless you put transaction control commands in
> there).
> > In extended query mode, since you can only put one textual query per
> Parse
> > message, that maps to statement_timeout governing the total time from
> > initial Parse to Sync. Which is what it does.
>
> I've never thought about that. And I cannot imagine anyone is using
> that way in extended protocol to simulate multi-statement queries. Is
> that documented somewhere?
>

Well, multiple parse/bind/execute messages before a sync are definitely
used by PgJDBC and nPgSQL for batching, and I just posted a patch for it
for libpq. I wouldn't have considered it to simulate multi-statement
simple-protocol queries, but I guess there are some parallels.

I am very surprised to find out that statement_timeout tracks the total
time and isn't reset by a new statement, but I guess it makes sense - what,
exactly, delimits a "query" in extended query mode? statement_timeout in
simple-query mode starts at parse time and runs until the end of execute.
In e.q.p. there might be only one parse, then a series of Bind and Execute
messages, or there may be repeated Parse messages.

Personally I'd be fairly happy with statement-timeout applying per-message
in the extended query protocol. That would mean that it behaves slightly
differently, and a query with a long slow parse and bind phase followed by
quick execution might fail to time out in the extended query protocol where
it would time out as a simple query. It'd behave as if the query was
PREPAREd then separately EXECUTEd in simple-query protocol. I'm not hugely
bothered by that, but if it's really a concern I'd ideally like to add a
new protocol message that resets the statement timeout counter, so the
client can define what delimits a statement. Not practical in the near term.

For now I'd be OK with documenting this as a quirk/limitation of
statement_timeout, that it applies to a whole extended-query-protocol
batch.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services