You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by "Meyer, Tobias" <to...@capgemini.com> on 2013/10/17 15:54:08 UTC
How to avoid ALTER SEQUENCE statement?
Hi,
we’re using OpenJPA 2.2.2 in WebSphere 8.5 and are faced with the following problem with the way OpenJPA handles native sequences.
Our Entities have IDs defined like this:
@Id
@SequenceGenerator(name = "MY_OID_SEQ", schema = "MY", sequenceName = "OID_SEQ", allocationSize = 1000)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_OID_SEQ")
@Column(name = "OID")
private long oid;
OpenJPA’s NativeJDBCSeq class seems to have no trust in the developers using native sequences and issues an “ALTER SEQUENCE MY.OID_SEQ INCREMENT BY 1000” statement the first time an object gets persisted/inserted.
This happens regardless of the fact that the sequence is already correctly defined.
Now the problem is that (of course) the application doesn’t have the rights to alter neither tables nor sequences, so the statement fails and the transaction rolls back (i.e. our object will not be persisted).
AND OpenJPA disables the caching of IDs completely resulting in massive performance issues when persisting large amounts of objects.
1. Giving the application the right to alter sequences is not an option, so how can we avoid that OpenJPA issues the ALTER SEQUENCE statement???
2. For further releases: PLEASE enable the developer to disable this easily, by setting a configuration value or something like that. And don’t issue an ALTER SEQUENCE statement if the sequence doesn’t need to be altered!
Thanks for your help,
Tobias
_______________________________________________________________________
Tobias Meyer
Custom Solution Development | Application Services
Capgemini | Hamburg
Phone: +4940254491291 – Mobile: +4915140250554
e-mail: tobias.meyer@capgemini.com
www.de.capgemini.com
Luebecker Strasse 128, D-22087 Hamburg, Germany
People matter, results count.
_______________________________________________________________________
________________________________
Firma: Capgemini Deutschland GmbH
Geschäftsführer: Dr. Michael Schulte (Sprecher) • Dr. Uwe Dumslaff • Josef Ranner
Aufsichtsratsvorsitzender: Antonio Schnieder
Amtsgericht Berlin-Charlottenburg, HRB 98814
This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
Re: How to avoid ALTER SEQUENCE statement?
Posted by Heath Thomann <jp...@gmail.com>.
Hello! A patch to allow the alter to be disabled is being worked on.
Please see the following JIRA:
https://issues.apache.org/jira/browse/OPENJPA-2450
Thanks,
Heath
On Thu, Oct 17, 2013 at 8:49 AM, Meyer, Tobias
<to...@capgemini.com>wrote:
> Hello John,
>
> you misunderstood.
> The Sequence is defined as follows:
>
> CREATE SEQUENCE MY.OID_SEQ AS BIGINT
> START WITH 100000
> INCREMENT BY 1000
> CYCLE
> CACHE 10;
>
> We were using identity columns earlier but switched to dedicated sequences
> to benefit from the ability to cache numerous ID values in advance to
> reduce database accesses.
>
>
> Tobias
>
> -----Original Message-----
> From: Boblitz John [mailto:john.boblitz@bertschi.com]
> Sent: Donnerstag, 17. Oktober 2013 16:43
> To: 'users@openjpa.apache.org'
> Subject: RE: How to avoid ALTER SEQUENCE statement?
>
> Hello Tobias,
>
> maybe I misunderstand, but is your sequence defined in the DB Table itself?
>
> If it is, I think your need to use the GenerationType.IDENTITY -
>
> if not, please disregard.
>
> Cheers,
>
> John
>
> > -----Original Message-----
> > From: Meyer, Tobias [mailto:tobias.meyer@capgemini.com]
> > Sent: Thursday, October 17, 2013 3:54 PM
> > To: users@openjpa.apache.org
> > Subject: How to avoid ALTER SEQUENCE statement?
> >
> > Hi,
> >
> > we’re using OpenJPA 2.2.2 in WebSphere 8.5 and are faced with the
> following
> > problem with the way OpenJPA handles native sequences.
> >
> > Our Entities have IDs defined like this:
> > @Id
> > @SequenceGenerator(name = "MY_OID_SEQ", schema = "MY", sequenceName
> > = "OID_SEQ", allocationSize = 1000) @GeneratedValue(strategy =
> > GenerationType.SEQUENCE, generator = "MY_OID_SEQ") @Column(name =
> > "OID") private long oid;
> >
> > OpenJPA’s NativeJDBCSeq class seems to have no trust in the developers
> using
> > native sequences and issues an “ALTER SEQUENCE MY.OID_SEQ INCREMENT
> > BY 1000” statement the first time an object gets persisted/inserted.
> > This happens regardless of the fact that the sequence is already
> correctly
> > defined.
> >
> > Now the problem is that (of course) the application doesn’t have the
> rights to
> > alter neither tables nor sequences, so the statement fails and the
> transaction
> > rolls back (i.e. our object will not be persisted).
> > AND OpenJPA disables the caching of IDs completely resulting in massive
> > performance issues when persisting large amounts of objects.
> >
> > 1. Giving the application the right to alter sequences is not an option,
> so how
> > can we avoid that OpenJPA issues the ALTER SEQUENCE statement???
> > 2. For further releases: PLEASE enable the developer to disable this
> easily, by
> > setting a configuration value or something like that. And don’t issue an
> ALTER
> > SEQUENCE statement if the sequence doesn’t need to be altered!
> >
> > Thanks for your help,
> > Tobias
> >
> >
> > ________________________________________________________________
> > _______
> > Tobias Meyer
> > Custom Solution Development | Application Services
> >
> > Capgemini | Hamburg
> > Phone: +4940254491291 – Mobile: +4915140250554
> > e-mail: tobias.meyer@capgemini.com
> > www.de.capgemini.com
> >
> > Luebecker Strasse 128, D-22087 Hamburg, Germany People matter, results
> > count.
> > ________________________________________________________________
> > _______
> >
> >
> > ________________________________
> >
> > Firma: Capgemini Deutschland GmbH
> > Geschäftsführer: Dr. Michael Schulte (Sprecher) • Dr. Uwe Dumslaff •
> Josef
> > Ranner
> > Aufsichtsratsvorsitzender: Antonio Schnieder Amtsgericht Berlin-
> > Charlottenburg, HRB 98814 This message contains information that may be
> > privileged or confidential and is the property of the Capgemini Group.
> It is
> > intended only for the person to whom it is addressed. If you are not the
> > intended recipient, you are not authorized to read, print, retain, copy,
> > disseminate, distribute, or use this message or any part thereof. If you
> receive
> > this message in error, please notify the sender immediately and delete
> all
> > copies of this message.
> This message contains information that may be privileged or confidential
> and is the property of the Capgemini Group. It is intended only for the
> person to whom it is addressed. If you are not the intended recipient, you
> are not authorized to read, print, retain, copy, disseminate, distribute,
> or use this message or any part thereof. If you receive this message in
> error, please notify the sender immediately and delete all copies of this
> message.
>
RE: How to avoid ALTER SEQUENCE statement?
Posted by "Meyer, Tobias" <to...@capgemini.com>.
Hello John,
you misunderstood.
The Sequence is defined as follows:
CREATE SEQUENCE MY.OID_SEQ AS BIGINT
START WITH 100000
INCREMENT BY 1000
CYCLE
CACHE 10;
We were using identity columns earlier but switched to dedicated sequences to benefit from the ability to cache numerous ID values in advance to reduce database accesses.
Tobias
-----Original Message-----
From: Boblitz John [mailto:john.boblitz@bertschi.com]
Sent: Donnerstag, 17. Oktober 2013 16:43
To: 'users@openjpa.apache.org'
Subject: RE: How to avoid ALTER SEQUENCE statement?
Hello Tobias,
maybe I misunderstand, but is your sequence defined in the DB Table itself?
If it is, I think your need to use the GenerationType.IDENTITY -
if not, please disregard.
Cheers,
John
> -----Original Message-----
> From: Meyer, Tobias [mailto:tobias.meyer@capgemini.com]
> Sent: Thursday, October 17, 2013 3:54 PM
> To: users@openjpa.apache.org
> Subject: How to avoid ALTER SEQUENCE statement?
>
> Hi,
>
> we’re using OpenJPA 2.2.2 in WebSphere 8.5 and are faced with the following
> problem with the way OpenJPA handles native sequences.
>
> Our Entities have IDs defined like this:
> @Id
> @SequenceGenerator(name = "MY_OID_SEQ", schema = "MY", sequenceName
> = "OID_SEQ", allocationSize = 1000) @GeneratedValue(strategy =
> GenerationType.SEQUENCE, generator = "MY_OID_SEQ") @Column(name =
> "OID") private long oid;
>
> OpenJPA’s NativeJDBCSeq class seems to have no trust in the developers using
> native sequences and issues an “ALTER SEQUENCE MY.OID_SEQ INCREMENT
> BY 1000” statement the first time an object gets persisted/inserted.
> This happens regardless of the fact that the sequence is already correctly
> defined.
>
> Now the problem is that (of course) the application doesn’t have the rights to
> alter neither tables nor sequences, so the statement fails and the transaction
> rolls back (i.e. our object will not be persisted).
> AND OpenJPA disables the caching of IDs completely resulting in massive
> performance issues when persisting large amounts of objects.
>
> 1. Giving the application the right to alter sequences is not an option, so how
> can we avoid that OpenJPA issues the ALTER SEQUENCE statement???
> 2. For further releases: PLEASE enable the developer to disable this easily, by
> setting a configuration value or something like that. And don’t issue an ALTER
> SEQUENCE statement if the sequence doesn’t need to be altered!
>
> Thanks for your help,
> Tobias
>
>
> ________________________________________________________________
> _______
> Tobias Meyer
> Custom Solution Development | Application Services
>
> Capgemini | Hamburg
> Phone: +4940254491291 – Mobile: +4915140250554
> e-mail: tobias.meyer@capgemini.com
> www.de.capgemini.com
>
> Luebecker Strasse 128, D-22087 Hamburg, Germany People matter, results
> count.
> ________________________________________________________________
> _______
>
>
> ________________________________
>
> Firma: Capgemini Deutschland GmbH
> Geschäftsführer: Dr. Michael Schulte (Sprecher) • Dr. Uwe Dumslaff • Josef
> Ranner
> Aufsichtsratsvorsitzender: Antonio Schnieder Amtsgericht Berlin-
> Charlottenburg, HRB 98814 This message contains information that may be
> privileged or confidential and is the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not the
> intended recipient, you are not authorized to read, print, retain, copy,
> disseminate, distribute, or use this message or any part thereof. If you receive
> this message in error, please notify the sender immediately and delete all
> copies of this message.
This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
RE: How to avoid ALTER SEQUENCE statement?
Posted by Boblitz John <jo...@bertschi.com>.
Hello Tobias,
maybe I misunderstand, but is your sequence defined in the DB Table itself?
If it is, I think your need to use the GenerationType.IDENTITY -
if not, please disregard.
Cheers,
John
> -----Original Message-----
> From: Meyer, Tobias [mailto:tobias.meyer@capgemini.com]
> Sent: Thursday, October 17, 2013 3:54 PM
> To: users@openjpa.apache.org
> Subject: How to avoid ALTER SEQUENCE statement?
>
> Hi,
>
> we’re using OpenJPA 2.2.2 in WebSphere 8.5 and are faced with the following
> problem with the way OpenJPA handles native sequences.
>
> Our Entities have IDs defined like this:
> @Id
> @SequenceGenerator(name = "MY_OID_SEQ", schema = "MY", sequenceName
> = "OID_SEQ", allocationSize = 1000) @GeneratedValue(strategy =
> GenerationType.SEQUENCE, generator = "MY_OID_SEQ") @Column(name =
> "OID") private long oid;
>
> OpenJPA’s NativeJDBCSeq class seems to have no trust in the developers using
> native sequences and issues an “ALTER SEQUENCE MY.OID_SEQ INCREMENT
> BY 1000” statement the first time an object gets persisted/inserted.
> This happens regardless of the fact that the sequence is already correctly
> defined.
>
> Now the problem is that (of course) the application doesn’t have the rights to
> alter neither tables nor sequences, so the statement fails and the transaction
> rolls back (i.e. our object will not be persisted).
> AND OpenJPA disables the caching of IDs completely resulting in massive
> performance issues when persisting large amounts of objects.
>
> 1. Giving the application the right to alter sequences is not an option, so how
> can we avoid that OpenJPA issues the ALTER SEQUENCE statement???
> 2. For further releases: PLEASE enable the developer to disable this easily, by
> setting a configuration value or something like that. And don’t issue an ALTER
> SEQUENCE statement if the sequence doesn’t need to be altered!
>
> Thanks for your help,
> Tobias
>
>
> ________________________________________________________________
> _______
> Tobias Meyer
> Custom Solution Development | Application Services
>
> Capgemini | Hamburg
> Phone: +4940254491291 – Mobile: +4915140250554
> e-mail: tobias.meyer@capgemini.com
> www.de.capgemini.com
>
> Luebecker Strasse 128, D-22087 Hamburg, Germany People matter, results
> count.
> ________________________________________________________________
> _______
>
>
> ________________________________
>
> Firma: Capgemini Deutschland GmbH
> Geschäftsführer: Dr. Michael Schulte (Sprecher) • Dr. Uwe Dumslaff • Josef
> Ranner
> Aufsichtsratsvorsitzender: Antonio Schnieder Amtsgericht Berlin-
> Charlottenburg, HRB 98814 This message contains information that may be
> privileged or confidential and is the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not the
> intended recipient, you are not authorized to read, print, retain, copy,
> disseminate, distribute, or use this message or any part thereof. If you receive
> this message in error, please notify the sender immediately and delete all
> copies of this message.