openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marco de Booij <mdeb...@scarlet.be>
Subject Re: Postgres sequence: current transaction is aborted
Date Tue, 19 Jun 2012 20:07:47 GMT
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