Re: Issues with calling stored prcedures

Tim,

I suspect the problem is that your stored procedures are generating
notice messages to the client. These notice messages get queued up in
the jdbc driver as warning objects. I bet if you called getWarnings()
you would see all of the notice messages. That would be your 'memory leak'.

In the current code these warnings are queued per statement object, but
in older versions of the code they are queued per connection.

thanks,
--Barry

Tim McAuley wrote:
> 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.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>