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<[email protected]> wrote:
