Uploaded image for project: 'Camel'
  1. Camel
  2. CAMEL-16761

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

    XMLWordPrintableJSON

    Details

    • Estimated Complexity:
      Unknown

      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 ?

        Attachments

        1. sample batch insert.png
          35 kB
          Govind Singh

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              simply_govind Govind Singh
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: