Search code examples
postgresqltimeoutconnection-stringpostgresql-9.3

Postgres timing out 20-30 seconds despite connection string?

I am attempting to do some very basic queries using a product called AutoTag.

My PostgreSQL is installed locally (extremely beefy machine, SSD, 16GB RAM, blah blah).

One of the tables has 40 million plus records (and will be growing). I am trying to do a simple query which, when done from the Postgres Admin tools, takes 4-5 minutes to run.

My connection string attempts have been variations of:

PORT=5432;CommandTimeout=5000;TIMEOUT=1024;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE=2.2.0.0;HOST=localhost;DATABASE=HistoricalData;USER ID=postgres;PASSWORD=passwordhere

The connector (Windows environment) is: NpgsqlDdexProvider-2.2.0-VS2013 ...found at: https://github.com/npgsql/Npgsql/releases (using the "installable" version someone provided at the same site due to an "Assemblies" annoyance that AutoTag maker WIndward could not get around)

Every attempt to query from within Excel using AutoTag results in an apparent timeout in appx 20-30 seconds, which I'll paste below, despite my connection string above (tried TIMEOUT=0, won't connect.. tried no TIMEOUT, doesn't matter).

Help?! I'm apparently limited on connectivity - I have to use 64bit Excel with the 64bit version of AutoTag, and another connector I can use FINE with Crystal Reports won't work (filename psqlodbc_08_04_0200).

Exception stack:
ERROR: 57014: canceling statement due to statement timeout (DataSourceException)
ERROR: 57014: canceling statement due to statement timeout (NpgsqlException)

AutoTag version: 13.1.12.0
Office: 15 (64-bit), EXCEL
Windows: Microsoft Windows NT 6.1.7601 Service Pack 1 (64-bit), GC memory: 53,792,440

Stack trace:

Exception: net.windward.env.DataSourceException

Message: ERROR: 57014: canceling statement due to statement timeout 

Stack:  at net.windward.xmlreport.a.a(ArrayList A_0, e A_1, ArrayList A_2, ProcessReportAPI A_3)        

        at net.windward.xmlreport.ProcessReport.processData(Map dataProviders)

        at net.windward.xmlreport.ProcessReport.processData(DataSourceProvider datasourceProvider, String datasourceName)

        at WindwardArrow.net.windward.arrow.tools.d.a(b A_0, a A_1, a A_2, DrillDownInfo A_3, String A_4, Stream A_5, Stream A_6)

        at AutoTagCore.net.windward.autotag.controls.reports.c.a()

Exception: Npgsql.NpgsqlException

Message: ERROR: 57014: canceling statement due to statement timeout 

Stack:  at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()      

        at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)

        at Npgsql.ForwardsOnlyDataReader.GetNextRow(Boolean clearPending)

        at Npgsql.ForwardsOnlyDataReader.Read()

        at System.Data.Common.DbEnumerator.MoveNext()

        at WindwardReportsDrivers.net.windward.datasource.ado.AdoDataSource.AdoNode.AdoIterator.next()

Final Update: Highlighted the problem in the Connection String above per the solution provided.

Solution

  • I'd say exactly 20 seconds since you have COMMANDTIMEOUT twice in your connection string and the second one is set to 20 seconds.