openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Sutter <kwsut...@gmail.com>
Subject Re: Postgres sequence: current transaction is aborted
Date Wed, 20 Jun 2012 13:31:34 GMT
Hi John,
In this case, Marco was using a database Sequence, not the OpenJPA sequence
table.  There was an issue with having permissions to alter the Sequence
with Postgres.  Just wanted to clarify.  Thanks!

Kevin

On Wed, Jun 20, 2012 at 12:57 AM, Boblitz John <John.Boblitz@bertschi.com>wrote:

> You can set the permissions for the table with:
>
> GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE
> [SchemaName].openjpa_sequence_table TO [UserName];
>
> *NOTE:  This Sequence Table was autogenerated by openJPA - you name might
> be different!
>
>
> John
>
>
> > -----Urspr√ľngliche Nachricht-----
> > Von: Marco de Booij [mailto:mdebooy@scarlet.be]
> > Gesendet: Dienstag, 19. Juni 2012 22:08
> > An: users@openjpa.apache.org
> > Betreff: Re: Postgres sequence: current transaction is aborted
> >
> > Hello Kevin,
> >
> > Thanks for the help. Your work around solved my problem. I
> > made my application user the owner of the sequence and I was
> > able to insert my rows. I could not find how/if I could grant
> > the rights.
> >
> > I hope that the 'final' solution works without this ALTER
> > SEQUENCE statement. I personally do not like to use the owner
> > of objects in a datasource. Applications should not be
> > allowed to change database objects. They only should change
> > data. However if you do not give a SQL script with your
> > application then you need to have openJPA creating the objects :-)
> >
> > There is 1 big disadvantage. With increment of 50 you get big
> > holes in your primary keys. My first key was 53 (the current
> > value was 3 before I
> > started) and after I restarted Tomee and inserted another row
> > the primary key went from 171 to 203. I set the
> > allocationSize to 2 for smaller holes.
> >
> > Regards,
> >
> > Marco
> >
> > Op 19-06-12 00:06, Kevin Sutter schreef:
> > > Hi Marco,
> > > The trace from Postgres is helping to understand the issue.
> >  I think
> > > the basic problem is that the application doesn't have proper
> > > permissions to update (alter) the sequence:
> > >
> > > 2012-06-18 20:17:28 CEST ERROR:  must be owner of relation
> > > seq_i18n_codes
> > > 2012-06-18 20:17:28 CEST STATEMENT:  ALTER SEQUENCE
> > > DOOS.SEQ_I18N_CODES INCREMENT BY 50
> > >
> > > If the permissions for this sequence can be set to allow for this
> > > "alter sequence..." statement, then you would be in the clear.
> > > Unfortunately, I am not a Postgres expert, so I don't know
> > the magic
> > > incantation to allow for this.  But, if this can be figured
> > out then
> > > you would be in much better shape.
> > >
> > > It looks like this whole issue is coming about due to the changes
> > > introduced with OpenJPA-1376 and OpenJPA-2069.  The
> > configuration of
> > > sequences was not properly implemented in the first place.  The
> > > parameters were not properly applied to the sequence creation.  As
> > > part of that fix, it was determined to always execute the "alter
> > > sequence.." statement to ensure that the sequence in the database
> > > matched the expectations of the sequence definition.  This "alter
> > > sequence.." statement must be acceptable to all of the other
> > > databases, just not Postgres due to the permissions thing.
> > >
> > > As a side issue...  Your idea of setting the allocationSize to 1
> > > should have been a good workaround.  Unfortunately, the
> > generation of
> > > the "alter sequence.." statement has a problem -- as you have
> > > discovered.  Since the allocationSize is not greater than
> > 1, we quit
> > > generating the "alter sequence.." statement and we end up with the
> > > syntax error as you reported below (OpenJPA-2196).  And,
> > since we blindly issue the "alter sequence.."
> > > statement, there is not a way to disable the generation and
> > execution
> > > of this statement.  So, two issues surfaced with that workaround...
> > >
> > > All of this is saying that I don't have a quick answer for
> > you...  If
> > > the owner permission thing can be figured out for Postgres, that's
> > > going to be the quickest workaround.  Otherwise, it looks
> > like we have
> > > 2 or 3 problems that need a JIRA resolution.
> > >
> > > Kevin
> > >
> > > On Mon, Jun 18, 2012 at 3:17 PM, Marco de
> > Booij<mdebooy@scarlet.be>  wrote:
> > >
> >
> >
>

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