Details
Major Description
I am facing a strange behavior in Camel.
The use-case is batch insert into a table (student) and then just log the autogenerated Primary keys from that table. Database is Postgres. Note to keep it simple, this table has 2 columns, id (auto-generatedprimary key) & name (varchar 256). The (simplified) code is:
{{}}
from("direct:test_autogenerated_pk") .log("test autogenerated pk") .loop(3) .process(ex -> { Integer outerIteration = (Integer) ex.getProperty(Exchange.LOOP_INDEX); log.info("Iteartion : {}", outerIteration); final var iterator = IntStream.range(0, 2).boxed().map(x -> { final var query = new HashMap<String, Object>(); Integer innerIteration = x.intValue(); query.put("studentName", "abc_" + outerIteration + "_" + innerIteration); return query; }).iterator(); ex.getMessage().setBody(iterator); ex.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true); }) .to("sqlComponent:INSERT INTO student (name) VALUES (:#studentName);?batch=true") .setProperty("insertedIds", simple("${headers.CamelSqlGeneratedKeyRows}")) .process(ex -> { ArrayList<LinkedCaseInsensitiveMap> metadataList = (ArrayList<LinkedCaseInsensitiveMap>)ex.getIn().getExchange().getProperty("insertedIds"); for (LinkedCaseInsensitiveMap metadata : metadataList) { Integer id = (Integer) metadata.get("id"); log.info("student ID: {}", id); } }) .end();
{{}}
To keep the example simple, we iterate 3 times batch inserting 2 rows per iteration.
Expected behavior: Every (batch) insert returns newly auto-generated Primary keys per iteration
Actual behavior: Only the auto-generated primary keys in the first iteration are returned in every iteration
Related logs:
{{}}
[INFO ] 2021-06-28 10:45:35.991 [Test worker] MyRouteBuilder - Iteartion : 0 [INFO ] 2021-06-28 10:45:36.158 [Test worker] MyRouteBuilder - student ID: 8480 [INFO ] 2021-06-28 10:45:36.159 [Test worker] MyRouteBuilder - student ID: 8481 [INFO ] 2021-06-28 10:45:36.159 [Test worker] MyRouteBuilder - Iteartion : 1 [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - student ID: 8480 [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - student ID: 8481 [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - Iteartion : 2 [INFO ] 2021-06-28 10:45:36.174 [Test worker] MyRouteBuilder - student ID: 8480 [INFO ] 2021-06-28 10:45:36.175 [Test worker] MyRouteBuilder - student ID: 8481
{{}}
ID 8480 & 8481 are the auto-genearated primary keys in the first iteration.
Note: Auto generated PK's do work, if I don't use the batch insert logic, that is, without an Iterator and inserting only 1 row per iteration. But that is of course, extremely slow.
Any suggestion/help here ?
