SET statement_timeout to '4s';
will set the upper limit of execution time for each subsequent query to 4 seconds. So,
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
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
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.