Thread: 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) {}
}
Craig,
This connection is in transaction mode, so you need to roll the
transaction back, or commit it in order to be able to use it again.
alternatively you can close it and see if poolman checks to see if it is
closed?
Dave
On Thu, 2002-06-20 at 13:38, 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
>
>
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
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
>