openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boblitz John <John.Bobl...@BERTSCHI.com>
Subject AW: Postgres sequence: current transaction is aborted
Date Wed, 20 Jun 2012 13:50:13 GMT
Hi Keven,

Oh.

The same GRANT will work though - no need to play with ownership.

Cheers!

John 

> -----Urspr√ľngliche Nachricht-----
> Von: Kevin Sutter [mailto:kwsutter@gmail.com] 
> Gesendet: Mittwoch, 20. Juni 2012 15:32
> An: users@openjpa.apache.org
> Betreff: Re: Postgres sequence: current transaction is aborted
> 
> 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
View raw message