referential integrity violation makes connection pool useless - Mailing list pgsql-jdbc

System: RedHat 7.1
Java: 1.4.0


I am running Tomcat 4.0.4 and using Poolman 2.0.4 to do connection
pooling with the JDBC 2.0 driver. When I try to delete something from a
particular table through a web application, the following SQLException
is thrown:

java.sql.SQLException: ERROR:  <unnamed> referential integrity violation
- key in user_products still referenced from cp_product

This was an expected result, as I want to stop a  product from being
deleted if it is being used else where. The problem comes that after the
exception is thrown, any query the web application tries to execute on
any connection from the connection pool results in the following exception:

No results were returned by the query.
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
        at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement
.java:99)

The only way to be able to execute queries is to reload the application,
which reinitializes the connection pool.

Any help on this would be appreciated. The code where the referential
integrity violation occurs is below.

try {
            //get connection from the connection pool
            conn = connManager.getConnection();
            if (conn == null) {
                throw new RepositoryException("deleteMerchantProducts
:connection was null");
            }
            pstmt =
conn.prepareStatement(this.getDeleteMerchantProductSQL());
            for (Iterator iterator = keys.iterator(); iterator.hasNext();) {
                Integer key= (Integer) iterator.next();
                pstmt.setInt(1, merchant.getKey());
                pstmt.setInt(2, key.intValue() );

                //delete product from database
                pstmt.executeUpdate();
                pstmt.clearParameters();
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new RepositoryException(e.toString());
        } finally {
            try { pstmt.close();} catch (Exception ignored) {}
            // return connection to pool
            try { connManager.returnConnection(conn);} catch (Exception
ignored) {}
        }