Closing connection but still getting Exception: null.Message:FATAL: too many connections for role "me"

Question

There are many similar questions but I am closing the connection in the finally block. I am testing so I am refreshing the same page often.

in the DAO ( which is called from the controller when the view is accessed)

try {
                con= DB.getConnection();
                st= connection.createStatement();
                rs = statement.executeQuery(MY_QUERY);

            while (rs.next()) {
                ...                 
                }               
         } catch (SQLException e ) {
             e.printStackTrace();    
        } finally {
             try { rs.close(); } catch (Exception e) { /* ignored */ }
             try { st.close(); } catch (Exception e) { /* ignored */ }
             try { conn.close(); } catch (Exception e) { /* ignored */ }
        }

in application.conf

 db.default.driver=org.postgresql.Driver
 db.default.url="jdbc:postgresql://hostname2/schema"
 db.default.user="myuser"
 db.default.password="mypass"

Inevitably after a few hours coding I hit the no more connections error. shouldn't the finally close the connection and return it to myuser's pool? Does hitting CTRL-D not close the connection?

Using: PostgreSQL, Java with Play2 framework, running with play run (testing/building stage)

UPDATE: still looking for a reason

score 1 · Answer 1 · answered Jul 28 '15 at 18:27

Here's some working database code from a project I'm working on:

try
{
        //Run a query.
        statement = connection.createStatement();
        statement.execute(db_request);
        results = statement.getResultSet();

        //Put the list of names into the table.
        table = getTableResults(results);
        if(table == null)
            return null;

        System.out.println("Running database command: " + db_request);

        //End.
        results.close();
        statement.close();
        connection.close();
}
catch (SQLException ex)
{
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());

        return null;
}

return table;

I run all the close statements at the end of the try block and only catch SQLException. If anything else is going on, the console prints the stack trace and shows me the exact line where it broke.

By the way, catch(Exception e) is a REALLY bad coding practice that causes Java to hide errors from you unless they're fatal. I imagine you'd get a lot more information from the stack trace that's automatically printed to the console if you removed those lines.

I feel like you need a finally. I agree with the Exception e — Jabda, Jul 28 '15 at 18:43
if anyone else can confirm having the close statements inside the try won't give an advantage over having them in finally. If anything goes wrong the finally will be called and it will be called if everything goes well. http://stackoverflow.com/questions/65035/does-finally-always-execute-in-java — Jabda, Jul 28 '15 at 19:35

score 1 · Answer 2 · answered Jul 28 '15 at 18:34

1

Seeing how Play Framework gives you play.Logger class, you could instrument that finally and the try {} catch {} inside it with

Logger.info("Something happened...");

and start getting the idea of whats happening for yourself. From top of my head - nothing looks wrong with your code. Do you know the max number of concurrent connections that your db supports btw? If its running in the cloud, there may be an artificial limitation as well.

answered Jul 28 '15 at 18:34

Evgheni Crujcov

470
2
8

The error specifically says for role myusername. If I ask the dba to reset my connections it works again – Jabda Jul 28 '15 at 18:43
Ask your dba to check the number of connections configured in your postgres for that role... "myusername". – Evgheni Crujcov Jul 28 '15 at 19:09

Closing connection but still getting Exception: null.Message:FATAL: too many connections for role "me"

2 Answers2