Issues with calling stored prcedures - Mailing list pgsql-jdbc

Hi,

A few days ago we found a memory leak (or what looked like one) under
our J2EE application running under JBoss.

I've managed to track it down to a section of code that calls a stored
procedure on our Postgresql database. Are there any known issues with
calling stored procedures through JDBC calls? We've been doing this for
a while but never ran any memory leak tests on the system. I am finding
an approximate leak of 10MB for 1000 calls to this code.

I have tried two different styles of calling the stored procedure with
no noticeable difference and also calling a plain SQL query. The SQL
query does not exhibit the same memory leak.

Should the complexity of the stored procedure have any bearing on the
calling java code (or the fact that the stored procedure calls other sub
functions?). I shouldn't have thought so but this stored procedure is
fairly complex.

Has anyone else seen behavior like this? It's baffling me currently.

Code snippets (statement and connections are closed after use):

// Original code

            PreparedStatement statement =
dbConnection.prepareStatement("select storedProcedureName(?, ?)");
            statement.setLong(1, longValue1.longValue());
            statement.setLong(2, longValue2.longValue());
            ResultSet rs = statement.executeQuery();

            if (rs.next())
            {
                int resultCount = rs.getInt("storedProcedureName");
            }

// Using modified code for calling the stored procedure.

            boolean autoCommitStatus = dbConnection.getAutoCommit();
            dbConnection.setAutoCommit(false);

            CallableStatement statement = dbConnection.prepareCall("{ ?
= call storedProcedureName(?, ?) }");
            statement.registerOutParameter(1, Types.INTEGER);
            statement.setLong(2, longValue1.longValue());
            statement.setLong(3, longValue2.longValue());
            statement.execute();

            int resultCount = statement.getInt(1);

            dbConnection.setAutoCommit(autoCommitStatus);


// Plain SQL query

            PreparedStatement statement =
dbConnection.prepareStatement("select count(*) from currentTable where
id = ?");
            statement.setLong(1, longValue1.longValue());
            ResultSet rs = statement.executeQuery();

            if (rs.next())
            {
                int resultCount = rs.getInt(1);
            }

Environment:
Postgresql 7.3.2 (Running on Redhat 9 Linux)
JBoss (3.2.2RC2), Java 1.4.2 (running on local Windows 2000 PC)

Original leak was exhibited on Linux using JBoss 3.0.7 using Java
1.4.1_02 and change of these two had not noticeable affect.

We are using our own JDBC driver compiled from the 7.3.2 sourcecode and
modified to handle long indexes. I have just tried the latest stable
driver from the jdbc.postgresql website and that has made no difference.