[jira] [Created] (CAMEL-16761) auto-generated primary keys are not retuned for Postgres when using loop iteration

["Govind Singh (Jira)" <ji...@apache.org>]

Govind Singh created CAMEL-16761:
------------------------------------

             Summary: auto-generated primary keys are not retuned for Postgres when using loop iteration
                 Key: CAMEL-16761
                 URL: https://issues.apache.org/jira/browse/CAMEL-16761
             Project: Camel
          Issue Type: Bug
          Components: camel-sql
    Affects Versions: 3.10.0
         Environment: OS: Mac OS Catalina

Camel: 3.10.0

Postgresql: 12.5
            Reporter: Govind Singh


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:
  
 
{{}}
{code:java}
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();{code}
{{}}

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:
 
{{}}
{code:java}
[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{code}
{{}}

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 ?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)