The Camel JDBC-based aggregator supports an optimistic locking strategy, to allow it to work in a clustered environment, where two or more Fuse instances share the same database tables for the aggregation store.

However, although it is relatively easy to configure the JDBC aggregation repository to work robustly with an Oracle database, with Postgresql it fails in a number of different ways.

These failures are only under load, that is, when the pattern of load creates a situation where multiple JDBC operations are likely to take place concurrently.

I'm assuming that it will be necessary to use an XA-compliant JDBC datasource for the database connection, and to mark the route as transacted. Many potential ways to configure such a datasource may be found, but they all fail, and all in different ways.

In my test, the Camel route consumes from an ActiveMQ broker, and aggregates the messages into threes, then prints the results. It's about as simple an example of aggregation that I could come up with. The route and datasource definitions are attached.

With the datasource definition in datasource.xml, the failure is consistently that shown in the attached exception1.txt. With the datasource definition in datasource2.xml, the exception is in exception2.txt. This latter is particularly interesting, since it appears that the aggregator is unable to recognize the exception thrown by the driver as representing a duplicate row insertion, despite the Postgresql documentation stating that it will match the required criteria.

In fact, I've tried many different ways to define the datasource and the transaction semantics, and all fail on Postgres (in different ways) whilst working on Oracle.