PostgreSQL: ERROR – canceling statement due to statement timeout

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

PostgreSQL Error like “canceling statement due to statement timeout” – I love this error message because the developer should know the tentative require total execution time of their queries or functions.

In a real-time application, we must analyze the average execution time for our queries so that System Admin or Database Admin can allocate resource workload appropriately.

I completely disagree with Developers for arguments like why we need to inform tentative execution time of our query?.

Because for example, today you execute your query and it took two mins so tomorrow, it might complete in around two mins only.

For this query, we can set timeout up to 5 mins, but if it is taking more than 5 mins, then the developer should check for that query.

So error like “canceling statement due to statement timeout” which helps a lot to identify this kind of long running query.

If your query starts to take more time, there are mainly two reasons:
1. Query is same, but the number of records increases in table
2. DBA forget to execute VACUUM ANALYZE or ANALYZE

So here my suggestion is, DBA can set a timeout value for mission critical query or we can also set threshold value so that we can check the status of a query before killing it.

In PostgreSQL, you can set the statement_timeout parameter at the Server level or Session level.
For server level, we have to modify PostgreSQL.conf file and for session level we can use below statement.