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:
>
|