Re: referential integrity violation makes connection

Hello,

The solution is that after any exception, you should close that connection
permanently and remove it from the pool. PostgreSQL JDBC does not seem to
work well after any error with a connection.

I would also recommend you stop using Poolman, which is end of life. I
would recommend Protomatter instead, which I have working perfectly, and it
is a much more lightweight solution as well.

In fact, the author added the necessary methods to allow you to explicitly
remove a connection from a pool permanently in this situation per my request.

Cheers,

Doug

At 01:38 PM 6/20/2002, Craig Moon wrote:
>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