referential integrity violation makes connection pool useless

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) {}
       }


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org