Statement Timeout: On Web Sessions ONLY

I have a website and on that website, certain reports are run using SQL via the PSQL ODBC Source. Any report where the SQL takes longer than 30 seconds to run seems to time out!

I have tried amending the statement_timeout settings within PostgreSQL and this hasn't helped. I have also attempted to set the statement timeout within the ODBC Data Source Administrator and that did not help either.

Can someone please advise as to why the statement timeouts would only affect the web sessions and nothing else?

The error shown in the web logs and PostgreSQL database logs is below:

16:33:16.06 System.Data.Odbc.OdbcException (0x80131937): ERROR [57014] ERROR: canceling statement due to statement timeout;

I don't think the issue is in relation to the statement timeout setting in the PostgreSQL config file itself. The reason I say this, is because, I don't get the 30 second statement timeout when running queries in PGAdmin, I only get the timeout when I'm running reports on my website. The website connects to the PostgreSQL database via a PSQLODBC Driver.

Nonetheless, I did try setting the statement timeout within the PostgreSQL config file and I set the statement timeout to 90 Seconds and this change had no impact on the reports, they were still timing out after 30 seconds (The change was applied as show statement_timeout; did show the updated value). Secondly, I tried editing the Connect Settings via the ODBC Data Source Administrator Data Source Options and I set the statement timeout in here using the following command: SET STATEMENT_TIMEOUT TO 90000

The change was applied but had no impact again and the statement still kept timing out.

I have also tried altering the user statement timeout via running a query similar to the below:

Alter User "MM" set statement_timeout = 90000;

This again had no impact. The web session logs show the following:

21:36:32.46 Report SQL Failed: ERROR [57014] ERROR: canceling statement due to statement timeout;
Error while executing the query
21:36:32.46 System.Data.Odbc.OdbcException (0x80131937): ERROR [57014] ERROR: canceling statement due to statement timeout;
Error while executing the query
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Odbc.OdbcCommand.ExecuteReader()
   at Path.PathWebPage.SQLtoJSON(String strSQL, Boolean fColumns, Boolean fFields, String strODBC, Boolean fHasX, Boolean fHasY, Boolean fIsChart, String strUser, String strPass)

I have tried amending the statement_timeout settings within Postgres and this hasn't helped

Please show what you tried, and what error message (if any) you got when you tried it. Also, after changing the setting, did show statement_timeout; reflect the change you tried to make? What does select source from pg_settings where name ='statement_timeout' show?

In the "stock" versions of PostgreSQL, a user is always able to change their session's setting for statement_timeout. Some custom compilations like those run by hosted providers might block such changes though (hopefully with a suitable error message).

There are several ways this could be getting set in a way that differs from the value specified in postgresql.conf. It could be set on a per-user basis or per-database basis using something like alter user "webuser" set statement_timeout=30000 (which will take effect next time that user logs on, or someone logs on to that database). It could be set in the connection string (although I don't know how that works in ODBC). Or if your app uses a centralized subroutine to establish the connection, that code could just execute a set statement_timeout=30000 SQL command on the connection before it hands it back to the caller.