Bug #491: ERROR: RelationClearRelation: relation using JDBC
Alex Korneyev (kornale@charlie.cns.iit.edu) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
ERROR: RelationClearRelation: relation using JDBC
Long Description
Hi,
I have done a little research and seems like this should have been fixed, but it is not. my understanding is that postgres keeps a some sort of a cursor open to the data.
Error happens when i do this:
rs = stmt.executeQuery(sqlString);
The error is fixed by restarting jboss, that is why i think the reference is being kept alive. i close all my connections, statements and result sets.
please let me know if this an error or i am doing something wrong. but i believe that this is a bug, since i have seen a lot of postings for this error.
alex korneyev
stack trace is bellow:
[Default] java.sql.SQLException: ERROR: RelationClearRelation: relation 125857 deleted while still in use
[Default] at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
[Default]
[Default] at org.postgresql.Connection.ExecSQL(Connection.java:356)
[Default]
[Default] at org.postgresql.jdbc2.Statement.execute(Statement.java:129)
[Default]
[Default] at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
[Default]
[Default] at org.jboss.pool.jdbc.StatementInPool.executeQuery(StatementInPool.java:141)
[Default]
[Default] at ch07.UserBean.isSystemUser(UserBean.java:219)
[Default]
[Default] at ch07.UserBean.ejbCreate(UserBean.java:248)
[Default]
[Default] at java.lang.reflect.Method.invoke(Native Method)
[Default]
[Default] at org.jboss.ejb.plugins.BMPPersistenceManager.createEntity(BMPPersistenceManager.java:136)
[Default]
[Default] at org.jboss.ejb.EntityContainer.createHome(EntityContainer.java:616)
[Default]
[Default] at java.lang.reflect.Method.invoke(Native Method)
[Default]
[Default] at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:843)
[Default]
[Default] at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:231)
[Default]
[Default] at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:154)
[Default]
[Default] at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:108)
[Default]
[Default] at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:135)
[Default]
[Default] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:307)
[Default]
[Default] at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:86)
[Default]
[Default] at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:103)
[Default]
[Default] at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:106)
[Default]
[Default] at org.jboss.ejb.EntityContainer.invokeHome(EntityContainer.java:420)
[Default]
[Default] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invokeHome(JRMPContainerInvoker.java:372)
[Default]
[Default] at java.lang.reflect.Method.invoke(Native Method)
[Default]
[Default] at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:241)
[Default]
[Default] at sun.rmi.transport.Transport$1.run(Transport.java:142)
[Default]
[Default] at java.security.AccessController.doPrivileged(Native Method)
[Default]
[Default] at sun.rmi.transport.Transport.serviceCall(Transport.java:139)
[Default]
[Default] at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:443)
[Default]
[Default] at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:643)
[Default]
[Default] at java.lang.Thread.run(Thread.java:484)
[Default]
Sample Code
private boolean isSystemUser( String loginID )
throws Exception{
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
try {
System.out.println("isSystemUser");
conn = DBUtils.getConnection();
String sqlString =
"select " + User.OID_MAPFIELD
+",upper("
+ User.USR_LOGIN_ID_MAPFIELD
+ ") "
+ " from users where upper("
+ User.USR_LOGIN_ID_MAPFIELD +") = upper('"+loginID+"')"; System.out.println("sqlString = " + sqlString );
stmt = conn.prepareStatement(sqlString);
rs = stmt.executeQuery(sqlString);
if ( rs.next() ) {
System.out.println("found user ... ");
System.out.println("user oid: " + rs.getLong(User.OID_MAPFIELD) );
return true;
} else {
return false;
}
}
catch( Exception e) {
e.printStackTrace();
throw new CreateException( e.getMessage() );
}
finally {
DBUtils.releaseConnection( conn, stmt, rs );
}
}
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
ERROR: RelationClearRelation: relation using JDBC
What PG version are you running?
If it's reasonably current (7.1.*) I would like to see a postmaster log
trace of the queries being issued by your application. (Turn on
debug_print_query in postgresql.conf to make this happen) The code
fragment you provided is not very helpful for understanding what the
backend is seeing.
regards, tom lane
Hmm. This seems to be a slightly different variant of the problem than
the one we fixed. You could avoid it by not letting your clients hold
open transactions while they're sitting idle. The path that's causing
a problem (or at least the problem I reproduced here) is
Backend 1 Backend 2
use table users;
commit; begin;
drop table users;
create new users table
use table users;
The problem is essentially that backend 1 is prepared to notice the
old users table having gone away when it does "begin", but not later
in its transaction. It's still trying to use its old cached relation
entry, which is for the now-deleted table.
This is a bug, agreed, but not one that I can easily back-patch a fix
for into 7.1.*.
Another possible workaround for you is to not drop and recreate the
users table. Couldn't you just truncate it?
regards, tom lane
Import Notes
Reply to msg id not found: HCEAIOLJLIGAAIJLNFNKMEIGCAAA.kornale@charlie.cns.iit.eduReference msg id not found: HCEAIOLJLIGAAIJLNFNKMEIGCAAA.kornale@charlie.cns.iit.edu | Resolved by subject fallback
I said:
The path that's causing
a problem (or at least the problem I reproduced here) is
Backend 1 Backend 2
use table users;
commit; begin;
drop table users;
create new users table
use table users;
I've committed a fix for this problem for 7.2.
regards, tom lane