Hi Keven, Oh.
The same GRANT will work though - no need to play with ownership. Cheers! John > -----Ursprüngliche Nachricht----- > Von: Kevin Sutter [mailto:kwsut...@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.bobl...@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:mdeb...@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<mdeb...@scarlet.be> wrote: > > > > > > > > > > > > >
