Re: referential integrity violation makes connection pool

Craig,

After an error raised from the server the connection will be unusable
until a rollback() is called on the connection. Your logic is just
returning the connection to the pool in the error case and is not
calling rollback before doing that.

thanks,
--Barry

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
>