Thursday, May 17, 2018

statement_timeout changed in PostgreSQL 11

PostgreSQL allows to set time limit for each SQL statement. For example,

SET statement_timeout to '4s';

will set the upper limit of execution time for each subsequent query to 4 seconds. So,

SELECT pg_sleep(5);

will cancel 4 seconds after.

ERROR: canceling statement due to statement timeout

Simple and pretty easy to understand.

But if it is applied to extended queries, things are not so simple any more. Each extended query is divided into multiple phases:

Parse: parse SQL statement

Bind: bind prameters to the parsed SQL statement

Execute: run the query

Sync: ask PostgreSQL to return the query results

So when the statement timeout fires? The answer is 4, not 3. Until sync message is recieved, the statement timeout will not be checked in extended queries.

Ok. Consider next example. We assume that statement timeout has been set to 4 seconds.

Parse: SELECT pg_sleep(2) (Query A)

Bind: bind prameters to the parsed SQL statement A

Execute: run the query A

Parse: SELECT pg_sleep(3) (Query B)

Bind B

Execute B

Sync

This time, the statment timeout will be fired even if each query A and B is finished within 4 seconds. This is not very intuitive behavior I think. More over, if duration log is enabled, the time for each query A and B will be 2 seconds and 3 seconds of course. So users will be confused because despite the fact that each query definitely finishes within 4 seconds, the statement timer is fired.

From PostgreSQL 11, this behavior will be changed. The statement timeout will be checked at the time when Execute message is issued (3 and 6 above). So the statement timer will not be fired in the example.

In summary, statement timeout in PostgreSQL 11 will show more intuitive behavior than previous releases.