[CAMEL-16761] camel-sql - auto-generated primary keys are not retuned for Postgres when using loop iteration

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.10.0
• Fix Version/s: 3.10.0, 3.11.0, 3.12.0
• Component/s: camel-sql
• Labels: batch camel-sql postgres sql

Environment

OS: Mac OS Catalina

Camel: 3.10.0

Postgresql: 12.5

• Estimated Complexity: Unknown
• Assignee: Unassigned
• Reporter: Govind Singh
• Created: 28/Jun/21 10:26
• Updated: 06/Sep/21 11:15
• Resolved: 06/Sep/21 11:15

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 ?