0

Using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit I use driver PostgresSQL Unicode(x64) version 13.00.00.00

I have a query that is executed through an ODBC connection in a Powershell 5.1 script. I use the Fill() method to retreive about 3500 records daily. When the script works, it takes 2-5 minutes to execute and retrieve data when it works. Problem is that the script "fails" half of the time approx. When this occurs, the Powershell script does stop only after 2h and 30 seconds.

We double checked the postgres logs and when this occurs, we see that the query successfully completed within 6 minutes always. I don't know what to look for. any Idea?

Below is the error got:

Executed as user: NT Service\SQLSERVERAGENT. A job step 
received an error at line 94 in a PowerShell script. The
corresponding line is '(New-Object system.Data.odbc.
odbcDataAdapter($cmd)).fill($ds) | out-null  '. Correct
the script and reschedule the job. The error information
returned by PowerShell is: 'Exception calling "Fill" with
"1" argument(s): "The connection has been disabled."  '.

Process Exit Code -1.

Not too familiar with postgreSQL.

Thanks!

0

This must be the TCP keepalive timeout: if there is no traffic on a TCP connection, the kernel will automatically start sending TCP keepalive packages after a while. If there is no response to those, it determines that the other side has hung up without closing the connection and reports an error message. The default time until the kernel starts sending these packages is two hours, which matches your observation.

Since PostgreSQL is not in the habit of hanging up on the client (look into the PostgreSQL log for a corresponding message), you must have a misconfigured firewall or router that cut off connections after a while. If you cannot fight that problem at the source, you might be able to work around it by reducing that keepalive idle time substantially.

One way to do that is to change the PostgreSQL parameter tcp_keepalives_idle to something like 120, which will cause it to send keepalive packages after two minutes of no traffic on the connection. Depending on which client you use, you might alternatively change the keepalive settings on the client side.

4
  • I'll check the ODBC Driver for client keepalive. The killing question is why this would occur sometimes and sometimes not? Is that all related with the total execution time of my query each time? Any idea about that "feeling random" failing issue?
    – Actarus
    May 11 at 14:07
  • Perhaps sometimes the query returns before a timeout strikes somewhere and sometimes not. May 11 at 14:46
  • Issue is still present Laurenz. I noticed that only when the query takes more than 300 seconds the connection get closed still after 2 hours. We use pgbouncer and inside the pgbouncer.ini, I can't see any settings set to 300 seconds. I'll look about a default settings being 300, but I'm litle pessimist here. Thanks
    – Actarus
    May 14 at 15:39
  • It is pretty clear that the problem is not in PostgreSQL, but in the network. Since you are using pgBouncer, set tcp_keepalive = on and tcp_keepidle = 120 in pgbouncer.ini. May 14 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.