Friday, May 18, 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:

  1. Parse: parse SQL statement
  2.  Bind: bind prameters to the parsed SQL statement
  3.  Execute: run the query
  4.  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.
  1. Parse: SELECT pg_sleep(2) (Query A)
  2.  Bind: bind prameters to the parsed SQL statement A
  3.  Execute: run the query A
  4. Parse: SELECT pg_sleep(3) (Query B)
  5. Bind B
  6.  Execute B
  7.  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.

Failover triggered by PostgreSQL shutdown

  Photo by brisch27 Ultra fast PostgreSQL down detection If a user connects to Pgpool-II and one of PostgreSQL servers is stopped by admin, ...