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.
