[
https://issues.apache.org/jira/browse/CAMEL-13951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16924026#comment-16924026
]
Grzegorz Grzybek commented on CAMEL-13951:
------------------------------------------
The considered scenario is (which may be contested):
{code:xml}
<bean id="repo"
class="org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository">
<property name="dataSource" ref="ds" />
<property name="transactionManager" ref="platformTransactionManager" />
</bean>
<camelContext xmlns="http://camel.apache.org/...";>
<route id="test">
<from uri="xxx" />
<transacted />
<aggregate completionSize="3" strategyRef="strategy"
aggregationRepositoryRef="repo" optimisticLocking="true">
<correlationExpression>...</correlationExpression>
<optimisticLockRetryPolicy ... />
<to uri="..." />
</aggregate>
</route>
</camelContext>
{code}
where both {{<transacted />}} and {{JdbcAggregationRepository}} use the same
Spring's PlatformTransactionmanager (which I don't think is ok, but that's the
case I worked on).
There are *two* possible solutions I've found.
h4. update count
{{org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback}}
is a creator templated by {{Integer}} returning number of affected (for INSERT
- inserted) rows using {{java.sql.PreparedStatement#executeUpdate()}}.
PosrgreSQL (I'm not sure if it's standard) [has this neat clause for INSERT
INTO
statement|https://www.depesz.com/2015/05/10/waiting-for-9-5-add-support-for-insert-on-conflict-do-nothingupdate/]
that allows us to do:
{code:sql}
insert into aggregation (id, exchange) values (?, ?) on conflict do nothing
{code}
Using this statement, instead of getting SQLState 23505, which makes connection
(and transaction) effectively unusable under PostgreSQL, we could return {{0}}
from
{{org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository#insert()}}
and handle optimistic locking scenario.
This would require only to add {{ON CONFLICT DO NOTHING}} to the generated SQL.
Of course on DERBY (using in camel-sql tests), we're getting:
{noformat}
Caused by: ERROR 42X01: Syntax error: Encountered "ON" at line 1, column 58.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(Unknown
Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 67 more
{noformat}
h4. nested transaction
{{org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository}} uses
*two* instances of
{{org.springframework.transaction.support.TransactionTemplate}}:
* read only called in
{{org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository#get()}}
* read-write called in {{add()}}, {{remove()}} and {{confirm()}}
*if* the transaction template used in {{add()}} used
{{org.springframework.transaction.TransactionDefinition#PROPAGATION_NESTED}}
instead of
{{org.springframework.transaction.TransactionDefinition#PROPAGATION_REQUIRED}},
nested transaction used by {{add()}} would be rolled back upon SQLState=23505
and {{org.springframework.dao.DuplicateKeyException}} would cause:
{code:java}
org.springframework.transaction.support.AbstractTransactionStatus#rollbackToHeldSavepoint()
...
java.sql.Connection#rollback(java.sql.Savepoint)
{code}
instead of
{code:java}
org.springframework.transaction.support.AbstractPlatformTransactionManager#doRollback()
...
java.sql.Connection#rollback()
{code}
then,
{{org.apache.camel.processor.aggregate.jdbc.DefaultJdbcOptimisticLockingExceptionMapper#isOptimisticLocking()}}
would still return {{true}} because error propagation wouldn't be affected.
> JdbcAggregationRepository doesn't work with PostgreSQL
> ------------------------------------------------------
>
> Key: CAMEL-13951
> URL: https://issues.apache.org/jira/browse/CAMEL-13951
> Project: Camel
> Issue Type: Improvement
> Components: camel-sql
> Affects Versions: 2.24.1, 3.0.0.RC1
> Reporter: Grzegorz Grzybek
> Assignee: Grzegorz Grzybek
> Priority: Major
>
> See:
> *
> https://www.postgresql.org/message-id/200609241203.59292.ralf.wiebicke%40exedio.com
> *
> https://groups.google.com/forum/#!msg/comp.databases.postgresql/NuXGXdFbA4g/LQ97ahowmxkJ
> *
> https://www.progress.com/blogs/solution-to-current-transaction-is-aborted-error-for-postgresql-compatibility-with-odbc-jdbc
> The problem is that JdbcAggregationRepository relies heavily on spring-jdbc's
> throwing org.springframework.dao.DataIntegrityViolationException according to
> its sql exception mapping.
> By default, DataIntegrityViolationException is thrown if SQLState starts with
> {{23}}. Check https://www.postgresql.org/docs/10/errcodes-appendix.html and
> see that:
> bq. 23505: unique_violation
> The _problem_ specific to PostgreSQL is that hitting such exception makes the
> connection (and surrounding Spring transaction) unusable. There's no way to
> _repeat_ an attempt to insert (or even check current) state of aggregation,
> because PostgreSQL will immediately throw:
> {noformat}
> Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
> at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
> ...
> {noformat}
> With SQLState - {{25P02}} == "in_failed_sql_transaction".
> The expected (by PostgreSQL) behavior is to rollback current transaction and
> start over.
> We're however in (potentially nested) TransactionTemplate and _rollback only_
> status is beyond (easy) control.
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
