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.

Answer 1

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.