openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Curtis <curti...@gmail.com>
Subject Re: Using PostgreSQL sequences with db user restrictions
Date Wed, 29 Jan 2014 16:27:53 GMT
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.limbeck@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*

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message