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
>