Forum OpenACS Q&A: Re: Postgres deadlock & maximum execution time

Posted by Gustaf Neumann on

This is an old unanswered query, but maybe someone might find the following helpful: PostgreSQL supports a configuration variable named statement_timeout for limiting the max execution time of a query. This value can be changed per SQL session.

Since the SQL session in OpenACS is typically reused for several queries via the connection pool, one should sandwich the potentially slow query by a code like this:

set old_statement_timeout [db_string _ {SHOW statement_timeout}]
db_dml _ {SET statement_timeout = '13s'}
-- .... your potentially slow queries here 
db_dml _ {SET statement_timeout = :old_statement_timeout}

... and probably wrap a transaction out such a code. The value for statement_timeout can be set as well in the configuration file, but then per-default all queries are limited by this - unless one raises the limit per query like above.

In general, it is desirable to limit the maximum execution time of SQL queries issued from web pages to ensure the liveliness of a system. In practice, developers make sure, that overlong queries do not happen.