Spring Boot server keeps hitting Postgres connection limit

Question

I have a simple spring boot application. It operates as such:

User makes a REST query
Look up in database
Return results

The problem I am getting is that after making around 97 requests, I get the message:

FATAL: remaining connection slots are reserved for non-replication superuser connections

My Postgres server has a 100 connection limit, so with 3 for super user, 97 makes sense. What I am confused with is why Spring Boot won't close my connections automatically, though I have been led to believe that it should according to other S/O posts.

Here is the code for my REST endpoint:

@RestController
@RequestMapping(value = "/myEndpoint")
public class CommentController {

    @Autowired
    // Where the business logic is
    private CommentJdbcService commentJdbcService;

    @Autowired
    private JdbcTemplate template;

    @ApiOperation(value = "Get a comment by ID")
    @RequestMapping(value = "/{comment_id}", produces = "application/json", method = RequestMethod.GET)
    public ResponseEntity getComment(@PathVariable String commentId) {
        List<Comment> result;
        try {
            result = commentJdbcService.get(comment_id, template.getDataSource().getConnection());
        } catch (Exception e) {
            return log(e, HttpStatus.INTERNAL_SERVER_ERROR, slf4jLogger, Error.Levels.ERROR);
        }
        return new ResponseEntity<>(result, HttpStatus.OK);
    }
}

And here is the code for my business logic:

@Service
public class CommentJdbcService {
    public List<Comment> get(String commentId, Connection connection) throws Exception {
        String getSql = "SELECT * FROM comment WHERE comment_id=?::UUID";
        PreparedStatement statement = connection.prepareStatement(getSql);
        statement.setString(1, commentId);
        List<Comment> comments = new ArrayList<>();
        ResultSet assetResult = statement.executeQuery();
        while (assetResult.next()){
            comments.add(build(assetResult, connection));
        }
        return comments;
    }
}

Based on this, why won't Spring Boot automatically close my connections?

alpert · Accepted Answer · 2016-07-28T23:37:09.587

The best practice is you should close your connection when you done with it:

@ApiOperation(value = "Get a comment by ID")
@RequestMapping(value = "/{comment_id}", produces = "application/json", method = RequestMethod.GET)
public ResponseEntity getComment(@PathVariable String commentId) {
    List<Comment> result;
    Connection con;
    try {
        con = template.getDataSource().getConnection();
        result = commentJdbcService.get(comment_id, con);
    } catch (Exception e) {
        return log(e, HttpStatus.INTERNAL_SERVER_ERROR, slf4jLogger, Error.Levels.ERROR);
    } finally {
        try {
            if (con != null)
                con.close();
        } catch(SQLException se) {
            se.printStackTrace();
        }
    }
    return new ResponseEntity<>(result, HttpStatus.OK);
}

And also your prepared statements and result sets:

@Service
public class CommentJdbcService {
    public List<Comment> get(String commentId, Connection connection) throws Exception {
        PreparedStatement statement;
        ResultSet assetResult;
        try {
            String getSql = "SELECT * FROM comment WHERE comment_id=?::UUID";
            statement = connection.prepareStatement(getSql);
            statement.setString(1, commentId);
            List<Comment> comments = new ArrayList<>();
            assetResult = statement.executeQuery();
            while (assetResult.next()){
                comments.add(build(assetResult, connection));
            }
            return comments;
        } catch (Exception e) {
            // do exception handling
        } finally {
            try {
                if (statement != null)
                    statement.close();
            } catch(SQLException se) {
                se.printStackTrace();
            }
            try {
                if (assetResult != null)
                    assetResult.close();
            } catch(SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Supposing I have other Business logic functions (get comment by user, etc) won't this close the connection for those functions as well? — Tanishq dubey, Jul 28 '16 at 23:48
Before that functions you should call con = template.getDataSource().getConnection(); and close your con when you done with it. If you get connections and never close them it is very normal that you encounter lots of open connections. Close resourses when you done with them. http://stackoverflow.com/questions/2225221/closing-database-connections-in-java — alpert, Jul 29 '16 at 05:08

Spring Boot server keeps hitting Postgres connection limit

1 Answers1