16

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?

  • To see the current timeout: SHOW statement_timeout; – User Oct 7 at 11:07
26

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
  • Neither setting is present in config file, so I'm using defaults. :( Any other ideas? – Andrzej Gis Feb 16 '17 at 12:39
  • @gisek sure, it could be something at the network level, either in your local IP stack or at the firewall. – Evan Carroll Feb 20 '17 at 23:17
4

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.

  • It's definitely not a custom compiled server - just regular stuff from apt-get. Could you please elaborate more abut firewall and gateway leads? How can I verify and fix it? – Andrzej Gis Feb 15 '17 at 16:24
-2

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

  • 2
    These are settings for the client side of a database connection and not settings that can be configured on the server to disconnect a client. OP is looking for server-side connection timeout settings. – John aka hot2use Aug 3 '18 at 6:24

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.