46

I set connection and command timeout to 10 minutes in my application (client side).

Than my application runs a simple query: SELECT pg_sleep(65)

On some servers it works fine, but other servers close the connection after 60 seconds.

Could this be some sort of PostgreSQL server configuration which limits timeouts and ignores my client settings?

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Andrzej Gis
  • 571
  • 1
  • 4
  • 3

3 Answers3

72

Yes, it's possible

There are two settings mentioned in the docs (idle_in_transaction_session_timeout is new to version 9.6x)

  • statement_timeout (integer)

    Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

    Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

  • idle_in_transaction_session_timeout (integer)

    Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

    The default value of 0 disables this feature.

It's important to not set the statement_timeout in postgresql.conf unless you want amusement.

Here is an example of it working

SET statement_timeout = 10000;
SET
test=# SELECT pg_sleep(15);
ERROR:  canceling statement due to statement timeout
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
6

Not out of the box. It would be pretty easy to compile a custom server that ignored your settings, though.

But much more likely is that your connections are being severed by a firewall or gateway that doesn't like idle connections.

If you have access to the server's log file, that should give a good clue. If the client says the server closed the connection unexpectedly, and the server says the client closed the connection unexpectedly, then it is probably something in between the client and the server which is actually severing the connection.

jjanes
  • 42,332
  • 3
  • 44
  • 54
-1

Yes It is possible. There are some Connection Parameters which you need to set.

Please refer below link. https://jdbc.postgresql.org/documentation/head/connect.html

loginTimeout connectTimeout socketTimeout cancelSignalTimeout

Kavita Patil
  • 11