Sequence Access Problem when upgrading from 3.2.2 to 3.2.6
I am in the process of moving from 3.2.2 to 3.2.6 and am using JSDK1.4.2, and PostgreSQL 7.4 on a Linux platform. I have had my full application running successfully on 3.2.2 and PostgreSQL 7.2 for a long time. I am now getting an error that never appeared in 3.2.2. I can read my data successfully via CMP. However, when creating a new record I use PostgreSQL's sequences to create the primary key. Within a transaction I open a connection to the datasource execute an SQL command to generate the next number in the sequence and then try to read the value from the returned record set. At the point where the .next() method is invoked in the record set the following error appears:
2005-05-06 22:00:15,110 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
SQL exception, cannot connect via JDBC to the PostgreSQL 7.2 database.
The error message is: org.postgresql.util.PSQLException: Connection is closed. Operation is not permitted.
The code has worked for a long time under 3.2.2 and even now I can successfully execute the SQL command via a JSP that establishes a connection with the database.
Some debug messages that I have placed in my code that appear in the log immediately before the error message are:
2005-05-06 22:00:15,082 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNext]
In UniqueIDBean, getNext method.
The datasource is: java:/SafetyDS
The table name is: com.beulah.safety.data.model.model_history
The column name is: null
2005-05-06 22:00:15,082 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The datasource is: java:/SafetyDS
The table name is: com.beulah.safety.data.model.model_history
The column name is: null
2005-05-06 22:00:15,082 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The JNDI context is: javax.naming.InitialContext@1c95a57
2005-05-06 22:00:15,082 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The datasource is: org.jboss.resource.adapter.jdbc.WrapperDataSource@195ff24
2005-05-06 22:00:15,083 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The connection is: org.jboss.resource.adapter.jdbc.WrappedConnection@4a39f6
2005-05-06 22:00:15,083 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The statement is: org.jboss.resource.adapter.jdbc.WrappedStatement@12348b3
2005-05-06 22:00:15,092 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The datasource is: org.jboss.resource.adapter.jdbc.WrapperDataSource@195ff24
The sequence name is: model_history01_id_seq
The SQL statement is: SELECT nextval('model_history01_id_seq')
2005-05-06 22:00:15,094 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The result set has been retrieved.
The result set is: org.postgresql.jdbc3.Jdbc3ResultSet@1771b9b
2005-05-06 22:00:15,094 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The result set has been retrieved.
The string value of the result set is: org.postgresql.jdbc3.Jdbc3ResultSet@1771b9b
2005-05-06 22:00:15,109 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The result set has been retrieved.
The meta data of the result set is: org.postgresql.jdbc3.Jdbc3ResultSetMetaData@e11c32
2005-05-06 22:00:15,110 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The result set has been retrieved.
The string value of the meta data of the result set is: org.postgresql.jdbc3.Jdbc3ResultSetMetaData@102484e
2005-05-06 22:00:15,110 DEBUG [com.beulah.mvc.controller.ejb.util.UniqueIDBean.getNextPsql72]
In UniqueIDBean, getNextPsql72 method.
The result set has been retrieved.
The number of columns in the result set is: 1
I have been held up for a long time on this problem and am at a loss as to what is going on. Can anyone give me some direction please? Is it likely to be a driver problem? I have already tried a number of alternatives, but it doesn't seem to make any difference. Or has some of the locking changed from 3.2.2 to 3.2.6?
Thank you in anticipation
Roger
