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:
> I searched further. Somebody mentioned to add allocationSize=1 to the
> @SequenceGenerator but this did not solve the problem. I now get a simple
> alter sequence statement that looks like the problem (OPENJPA-2196) Create
> Sequence Postgres 9.1. Hope that this helps you a bit more.
>
> 2012-06-18 22:16:18 CEST LOG: execute S_2: COMMIT
> 2012-06-18 22:16:18 CEST LOG: execute S_1: BEGIN
> 2012-06-18 22:16:18 CEST ERROR: syntax error at end of input at character
> 35
> 2012-06-18 22:16:18 CEST STATEMENT: ALTER SEQUENCE DOOS.SEQ_I18N_CODES
> 2012-06-18 22:16:18 CEST ERROR: current transaction is aborted, commands
> ignored until end of transaction block
> 2012-06-18 22:16:18 CEST STATEMENT: SELECT NEXTVAL('DOOS.SEQ_I18N_CODES')
> 2012-06-18 22:16:18 CEST LOG: execute S_3: ROLLBACK
>
> Regards,
>
> Marco
>
>
> Op 18-06-12 15:28, Kevin Sutter schreef:
>
>> Hi Marco,
>>
>> That 25P02 state from Postgres is so nebulous... It really doesn't help
>> explain the problem that Postgres is complaining about... We've had some
>> recent activity on the OpenJPA mailing lists related to Postgres and
>> Sequences. It seems that we have varying degrees of success with
>> different
>> customers.
>>
>> Can you post the complete stack when you receive this error? Have you
>> searched the log for other problems leading up to this Error? Have you
>> tried turning on Trace for both OpenJPA and Postgres? No guarantees, but
>> Trace might shed some light on the issue.
>>
>> The Junit bucket for OpenJPA has various tests relating to Id generation
>> and sequences, and these seem to work with Postgres... So, we might need
>> some assistance with narrowing in the test and debug setup to help
>> reproduce the problem and eventually solve it.
>>
>> Thanks,
>> Kevin
>>
>>
>
