How to set 'wait_timeout' value in postgresql as in MySQL?

Question

I am using Postgresql(9.6.3) and I need to set value for Wait_timeout variable. But I don't find any answers related to that or any equivalent variables that can be used in Postgresql instead of wait_timeout variable in MySQL.

     long wait_time = 0;
     ResultSet rs = null;
     try {
        Statement st = con.createStatement();
        rs = st.executeQuery("show variables like 'wait_timeout'");
        if(rs.next())
            wait_time = rs.getLong("Value");
        rs.close();
    } catch (SQLException e) {
        logger.error(e);
    }        
// wait time in SQl is maintained in seconds whereas here we need 
milliseconds
     return (wait_time*1000)/2;

I am getting null value in resultSet variable after executing the query. I have found a variable called Statement_timeout but I don't know whether, it is equivalent for it or not, as it may affect all other sessions where as wait_timeout in MySQL does not. Please suggest me a better solution. Thanks in advance.

There is no such timeout in PostgreSQL. What good should that be? — Laurenz Albe
– Laurenz Albe, Commented May 28, 2019 at 7:53
@LaurenzAlbe how do you deal with long-running CLI batch programs that need an open Postgres connection. My server keeps disconnecting my client because my client is intended to be idle. — CMCDragonkai
– CMCDragonkai, Commented Apr 18, 2020 at 7:41

JosMac · Accepted Answer · 2019-05-28 08:22:50Z

10

MySQL implements a lot of timeouts (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html).

MySQL has:

wait_timeout - The number of seconds the server waits for activity on a noninteractive connection before closing it. Default value 28800s = 8 hours.
Similar is "interactive_timeout" - The number of seconds the server waits for activity on an interactive connection before closing it.

PostgreSQL currently has:

"statement_timeout" - 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.
"idle_in_transaction_session_timeout" - Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds.

(https://www.postgresql.org/docs/11/runtime-config-client.html)

See for example here - https://dba.stackexchange.com/questions/164419/is-it-possible-to-limit-timeout-on-postgres-server

To be honest I so far did not need to fiddle with these timeouts in MySQL except for "connect_timeout". So I cannot give you direct advice about it. But maybe "idle_in_transaction_session_timeout" is what you need.

answered May 28, 2019 at 8:22

JosMac

2,3601 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Heyyou Over a year ago

Whether "idle_in_transaction_session_timeout" will not affect any other sessions? Because "statement_timeout" may affect other sessions in the application@JosMac

JosMac Over a year ago

@Kishore you can set these settings also for specific session only - see here: cybertec-postgresql.com/en/…

Jeremy Over a year ago

I doubt that idle_in_transaction_session_timeout is what you need. This is only used if a session has started a transaction and hasn't committed or rolled back. If there's no active transaction or if there's an active transaction and a query is running, this timeout will not apply.

CMCDragonkai Over a year ago

I have a similar situation. I have a CLI script where I constructed a connection which is often idle as it waits for other parts of the code to complete. I then receive OperationalError because the connection is idle, and there's no transaction either. And the server closes the connection. Tried various timeout settings, none of them work. The server just closes the connection. It's strange that this feature is not exposed as a configuration parameter.

CMCDragonkai Over a year ago

Another point of confusion is that psycopg2 supports connection pooling which seems to indicate that psycopg2 can maintain long-running idle connections (in its pool or perhaps its actually polling or reacting to exceptions by creating new connections whenever the idle ones dropout). If so, why can't I manually create a long-running idle connection for my own usage.

|

Collectives™ on Stack Overflow

How to set 'wait_timeout' value in postgresql as in MySQL?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related