[
https://issues.apache.org/jira/browse/OPENJPA-2419?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13733483#comment-13733483
]
Scott Parkerson commented on OPENJPA-2419:
------------------------------------------
So, after more investigation, here's what's happening. If a user is using
OpenJPA on PostgreSQL with a table with Native Sequences:
1. Without transactions enabled, i.e. no JTA or transaction wrapper, it works
as is.
2. With transactions enabled in the default config, the default connection
factory is used, and once the call to ALTER SEQUENCE is made, everything falls
apart after that.
3 With transactions enabled and setting openjpa.jdbc.DBDictionary to
(nativeSequenceType=1), the second connection is used. However, the second
connection has autoCommit disabled, which means that a transaction is started.
The call to ALTER SEQUENCE fails, and so does the call to get the SEQUENCE
value. In the end, this doesn't affect the JTA transaction, but it doesn't
matter because a SQLException bubbles up as a StoreException and fails the
original tranaction. (Plus, you don't get a sequence value, which is not what
you want.)
I've been giving this some thought, and it seems like there are two possible
ways out.
1. To leave the DBDictionary's nativeSequenceType set to its current value
(CONTIGUIOUS) which means you don't need a separate connection, ever. Detect
autoCommit; if true, then you can do without using SAVEPOINT ... ROLLBACK TO;
if false, then use the SAVEPOINT ... ROLLBACK TO code.
2. Set nativeSequenceType = 1 for PostgreSQL, and always use a separate
connection factory to deal with native sequences. I'd also recommend forcing
autoCommit in this case to TRUE, as then you can discard the need for the
SAVEPOINT ... ROLLBACK TO code.
I'm actually wondering if there is a better way to do sequence caching that
doesn't involve altering the sequence's DDL. I wonder if a happy medium might
be to just call nextval in succession multiple times. Of course, if you do
that, you cannot just use a counter, and assume you have the next 50 items;
you'd need to actually keep a small cache of values that would then be popped
off as they are used; when it's empty, you'd have to repeat the trip to the
database.
Any thoughts on where to go from here?
> Sequence Caching Attempt Failing in JTA Managed Environments with PostgreSQL
> ----------------------------------------------------------------------------
>
> Key: OPENJPA-2419
> URL: https://issues.apache.org/jira/browse/OPENJPA-2419
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.2.2
> Environment: Fuse (JBoss) ESB 6.0.0-redhat-024 (Karaf 2.3.0)
> OpenJPA 2.2.2
> Apache Aries 1.0.0 (JPA/JTA/JNDI/Blueprint)
> Reporter: Scott Parkerson
>
> About a year ago, there was a bug (OPENJPA-2196) that I contributed a patch
> to that deals with cases where OpenJPA's sequence caching cannot be used if
> the native sequence in the database is not owned by the role connecting to
> the database. This patch was included in OpenJPA 2.2.2.
> Since then, I've started using JTA-managed transactions in my container (the
> container being JBoss Fuse ESB, using Aries JPA/JNDI/JTA), and have hit the
> following snags with my previous fix:
> 1. When the attempt to ALTER SEQUENCE ... INCREMENT BY fails, it basically
> hoses the entire transaction, causing the next thing (which is to get the
> next value in the sequence) to fail because the transaction is now invalid
> and must be rolled back.
> 2. Trying to work around this using either ConnectionFactory2Name or the
> non-jta-data-source configuration items in my persistence.xml file seems to
> never matter, as ALL native sequences in OpenJPA are of type TYPE_CONTIGUOUS,
> and thus it will always choose the managed (jta-data-source or
> ConnectionFactoryName) methods to attempt to modify the sequence. I cannot
> see where it attempts to suspend the transaction, either.
> Perhaps there is a workaround, but I cannot see it. Does anyone else have any
> ideas on what could be done to make this work?
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
