I seem to remember a similar issue coming up some time back. Please take a look through OpenJPA-2069[1] to see if it is relevant to the issue that you've encountered.
[1] https://issues.apache.org/jira/browse/OPENJPA-2069 Thanks, Rick On Wed, Jan 29, 2014 at 10:17 AM, Anja <anja.limb...@it.nrw.de> wrote: > Hello, > > with our new db user restrictions a problem has come up concerning Postgres > sequences. > I am currently using OpenJPA 2.2.2 with PostgreSQL 9.2.4 in a Java SE 6 > application. > > All entities have sequence generators with allocationSize=1, e.g. > @Id > @SequenceGenerator(name="DATEN_ID_GENERATOR", sequenceName="SEQ_DATEN", > allocationSize=1) > @GeneratedValue(strategy=GenerationType.SEQUENCE, > generator="DATEN_ID_GENERATOR") > @Column(unique=true, nullable=false) > private Long id; > > In the DB all sequences are defined with increment=1, e.g. > CREATE SEQUENCE seq_daten > INCREMENT 1 > MINVALUE 0 > MAXVALUE 9223372036854775807 > START 36383531 > CACHE 1; > > Due to security policies the application may not alter the db schema. > Hence the application's db user has no super user rights and is not allowed > to own sequences, either. > That's why the "ALTER SEQUENCE SEQ_DATEN INCREMENT BY 1" statement fails. > This seems to disturb the transaction because it breaks at the following > "SELECT NEXTVAL('SEQ_DATEN')" statement. > This is the stacktrace: > > 21.01.2014 09:18:15 DEBUG: <t 8814509, conn 7258892> executing prepstmnt > 30252715 ALTER SEQUENCE SEQ_DATEN INCREMENT BY 1 > 21.01.2014 09:18:15 DEBUG: <t 8814509, conn 7258892> [32 ms] spent > 21.01.2014 09:18:15 WARN : Unable to cache sequence values for sequence > "SEQ_DATEN". Your application does not have permission to run an ALTER > SEQUENCE command. Ensure that it has the appropriate permission to run an > ALTER SEQUENCE command. > 21.01.2014 09:18:15 DEBUG: <t 8814509, conn 7258892> executing prepstmnt > 19606677 SELECT NEXTVAL('SEQ_DATEN') > 21.01.2014 09:18:15 DEBUG: <t 8814509, conn 7258892> [1 ms] spent > 21.01.2014 09:18:15 ERROR: > <openjpa-2.2.2-r422266:1468616 fatal general error> > org.apache.openjpa.persistence.PersistenceException: ERROR: current > transaction is aborted, commands ignored until end of transaction block > {prepstmnt 19606677 SELECT NEXTVAL('SEQ_DATEN')} [code=0, state=25P02] > at > org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4962) > at > > org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4922) > at > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136) > at > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110) > at > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62) > at > > org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:66) > at > org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160) > at > org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144) > at > > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:775) > at > org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493) > at > org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469) > at > > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:759) > at > > org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135) > at > > org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:592) > at > > org.apache.openjpa.kernel.StateManagerImpl.assignField(StateManagerImpl.java:683) > at > > org.apache.openjpa.kernel.StateManagerImpl.beforeAccessField(StateManagerImpl.java:1655) > at > > org.apache.openjpa.kernel.StateManagerImpl.accessingField(StateManagerImpl.java:1586) > at > de.nrw.it.registerportal.indexdatenimport.entity.Daten.pcGetid(Daten.java) > at > > de.nrw.it.registerportal.indexdatenimport.entity.Daten.getId(Daten.java:153) > ... > Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: > current > transaction is aborted, commands ignored until end of transaction block > {prepstmnt 19606677 SELECT NEXTVAL('SEQ_DATEN')} [code=0, state=25P02] > at > > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219) > at > > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:203) > at > > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:59) > at > > org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:1118) > at > > org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268) > at > > org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresPreparedStatement.executeQuery(PostgresDictionary.java:1019) > at > > org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268) > at > > org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1801) > at > > org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:258) > at > > org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.getSequence(NativeJDBCSeq.java:311) > at > > org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:235) > at > > org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:198) > at > > org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60) > ... 34 more > > > > We don't actually want to change the increment value within the database. > So I thought that we might get around the issue if OpenJPA compared a > sequence's increment value with the one in the db ("SELECT increment_by > FROM > <name of sequence>" for PostgreSQL). > If the values were equal, no ALTER SEQUENCE statement would have to be > executed. > The next sequence value should then be obtainable without an exception. > I added the increment_by query to NativeJDBCSeq.allocateInternal() to test > the idea. This is the new log output: > > 21.01.2014 09:31:03 DEBUG: <t 8814509, conn 25997996> executing prepstmnt > 33006972 SELECT increment_by FROM SEQ_DATEN; > 21.01.2014 09:31:03 DEBUG: <t 8814509, conn 25997996> [32 ms] spent > 21.01.2014 09:31:03 INFO : dbIncrement = 1 - jpaIncrement = 1 > 21.01.2014 09:31:03 DEBUG: <t 8814509, conn 25997996> executing prepstmnt > 27915337 SELECT NEXTVAL('SEQ_DATEN') > 21.01.2014 09:31:04 DEBUG: <t 8814509, conn 25997996> [60 ms] spent > 21.01.2014 09:31:04 DEBUG: <t 8814509, conn 25997996> executing prepstmnt > 7018102 SELECT increment_by FROM SEQ_HISTORIE; > ... > > So this would actually work with our Postgres version. > > Is there any chance OpenJPA could incorporate such a test prior to a > potential ALTER SEQUENCE statement? > > Any advice would be appreciated. > > Thanks, > Anja > > > > -- > View this message in context: > http://openjpa.208410.n2.nabble.com/Using-PostgreSQL-sequences-with-db-user-restrictions-tp7585893.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. > -- *Rick Curtis*
