[
https://issues.apache.org/jira/browse/CAMEL-13951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16923995#comment-16923995
]
Grzegorz Grzybek edited comment on CAMEL-13951 at 9/6/19 7:38 AM:
------------------------------------------------------------------
Here's the unit test that shows the problem:
{code:java}
@Test
public void optimisticLocking() throws Exception {
BasicDataSource pool = new org.apache.commons.dbcp2.BasicDataSource();
pool.setUrl("jdbc:postgresql://localhost:5432/reportdb");
pool.setUsername("admin");
pool.setPassword("admin);
pool.setMinIdle(2);
pool.setMaxTotal(10);
DataSource ds = pool;
PlatformTransactionManager ptm = new DataSourceTransactionManager(ds);
DefaultTransactionDefinition routeDef = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
routeDef.setReadOnly(false);
TransactionTemplate route = new TransactionTemplate(ptm, routeDef);
DefaultTransactionDefinition aggregationDefAdd = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
aggregationDefAdd.setReadOnly(false);
TransactionTemplate aggregationAdd = new TransactionTemplate(ptm,
aggregationDefAdd);
DefaultTransactionDefinition aggregationDefGet = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
aggregationDefGet.setReadOnly(true);
TransactionTemplate aggregationGet = new TransactionTemplate(ptm,
aggregationDefGet);
JdbcTemplate jdbc = new JdbcTemplate(ds);
route.execute(status1 -> {
jdbc.query("select messagetext from message", rs -> {
LOG.info("message fetched in external tt: " + rs.getString(1));
});
try {
aggregationAdd.execute(status2 -> {
int count = jdbc.update("insert into aggregation (id, exchange)
values (?, ?)", "1", "2".getBytes(StandardCharsets.UTF_8));
LOG.info("Count: {}", count);
return null;
});
} catch (DuplicateKeyException e) {
LOG.warn("Expected DuplicateKeyException: {}", e.getMessage(), e);
SQLException sqle = (SQLException) e.getCause();
LOG.warn("State: {}, code: {}", sqle.getSQLState(),
sqle.getErrorCode());
}
aggregationGet.execute(status2 -> {
jdbc.query("select messagetext from message", rs -> {
LOG.info("message fetched in internal tt: " + rs.getString(1));
});
return null;
});
return null;
});
}
{code}
was (Author: gzres):
Here's the unit test that shows the problem:
{code:java}
@Test
public void optimisticLocking() throws Exception {
BasicDataSource pool = new org.apache.commons.dbcp2.BasicDataSource();
pool.setUrl("jdbc:postgresql://localhost:5432/reportdb");
pool.setUsername("admin");
pool.setPassword("admin);
pool.setMinIdle(2);
pool.setMaxTotal(10);
DataSource ds = pool;
PlatformTransactionManager ptm = new DataSourceTransactionManager(ds);
DefaultTransactionDefinition routeDef = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
routeDef.setReadOnly(false);
TransactionTemplate route = new TransactionTemplate(ptm, routeDef);
// DefaultTransactionDefinition aggregationDefAdd = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_NESTED);
DefaultTransactionDefinition aggregationDefAdd = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
aggregationDefAdd.setReadOnly(false);
TransactionTemplate aggregationAdd = new TransactionTemplate(ptm,
aggregationDefAdd);
DefaultTransactionDefinition aggregationDefGet = new
DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED);
aggregationDefGet.setReadOnly(true);
TransactionTemplate aggregationGet = new TransactionTemplate(ptm,
aggregationDefGet);
JdbcTemplate jdbc = new JdbcTemplate(ds);
// <transacted>
route.execute(status1 -> {
jdbc.query("select messagetext from message", rs -> {
LOG.info("message fetched in external tt: " + rs.getString(1));
});
try {
aggregationAdd.execute(status2 -> {
int count = jdbc.update("insert into aggregation (id, exchange)
values (?, ?)", "1", "2".getBytes(StandardCharsets.UTF_8));
// int count = jdbc.update("insert into aggregation (id,
exchange) values (?, ?) on conflict do nothing", "1",
"2".getBytes(StandardCharsets.UTF_8));
LOG.info("Count: {}", count);
return null;
});
} catch (DuplicateKeyException e) {
LOG.warn("Expected DuplicateKeyException: {}", e.getMessage(), e);
SQLException sqle = (SQLException) e.getCause();
LOG.warn("State: {}, code: {}", sqle.getSQLState(),
sqle.getErrorCode());
}
aggregationGet.execute(status2 -> {
jdbc.query("select messagetext from message", rs -> {
LOG.info("message fetched in internal tt: " + rs.getString(1));
});
return null;
});
return null;
});
}
{code}
> JdbcAggregationRepository doesn't work with PostgreSQL
> ------------------------------------------------------
>
> Key: CAMEL-13951
> URL: https://issues.apache.org/jira/browse/CAMEL-13951
> Project: Camel
> Issue Type: Bug
> 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)
