If you’re having problems with SQL commands timing out in Aginity Workbench for any PostgreSQL database (Amazon Redshift, IBM Netezza, EMC Greenplum), this post will instruct you on how to fix the issue by upgrading the included Npgsql PostgreSQL driver library to version 2.0.13 beta, which fixes the issue.
If you attempt to set “Default query timeout” or “Query timeout” over 2147 seconds, Aginity Workbench doesn’t adhere to the new timeout due to a bug in the Npgsql library version 2.0.11.91 that Aginity is currently using. Aginity Workbench will still timeout after 48 minutes with the following message:
A timeout has occured. If you were establishing a connection, increase Timeout value in ConnectionString. If you were executing a command, increase the CommandTimeout value in ConnectionString or in your NpgsqlCommand object.
You can follow these instructions to fix this issue, by upgrading to the 2.0.13 beta version of Npgsql that fixes this bug.
Close all open instances of Aginity Workbench.
Download the .NET 2.0 version of the 2.0.13.91 Npgsql binaries from the official web site at this link.
Unzip the Npgsql archive and copy the Npgsql.dll file into your Aginity Workbench install directory, replacing the older file (optionally, instead of replacing the file, you can rename the old Npgsql.dll file to something like Npgsql.dll.old). For Aginity Workbench for Redshift Beta 2, the directory is at:
C:\Program Files (x86)\Aginity\Aginity Workbench for Redshift (beta)(x64)
Open the config file for Aginity Workbench in a text editor (like Notepad). For the current version of Aginity Workbench for Redshift beta 2, the file is located at:
C:\Program Files (x86)\Aginity\Aginity Workbench for Redshift (beta)(x64)\Aginity.RedshiftWorkbench.exe.config
Inside of the <configuration> tag, add the following lines:
<runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" /> <bindingRedirect oldVersion="2.0.11.91" newVersion="2.0.13.91" /> </dependentAssembly> </assemblyBinding> </runtime>
For example, your configuration file should now start like this:
<?xml version="1.0" encoding="utf-8"?> <configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" /> <bindingRedirect oldVersion="2.0.11.91" newVersion="2.0.13.91" /> </dependentAssembly> </assemblyBinding> </runtime> <appSettings>
Once you save the configuration file and start Aginity Workbench, you will now be using the newer version of Npgsql that will fix the timeout bug.
Update: As of 04/06/2015, Aginity has announced version 4.4.2183.656 of Agnity Workbench with version 2.2.5.0 of the Npgsql library, which should resolve this issue.