Skip to main content
DGaboleiro
DGaboleiro
Silver ⭐️⭐️
October 21, 2022
Question

Upsert on conflict not quoting keys

Hi,

I'm using a PutDatabaseRecord with UPSERT statement for a PostgreSQL database, but the ON CONFLICT keys are not being quoted event with the setting "Quote Column Identifiers" set to true.

DGaboleiro_0-1666334332405.png

ERROR:
Batch entry 0 INSERT INTO "eventstorage"."2062d999b1f26bcb32c3f39d22bd34"(..., "FDZ_UUID", ...) VALUES (...) ON CONFLICT (FDZUUID) DO UPDATE SET (...) = (..., EXCLUDED."FDZ_UUID", ...) was aborted: ERROR: column "fdzuuid" does not exist

I've seen an JIRA issue regarding this behavior and it is as Resolved: https://issues.apache.org/jira/browse/NIFI-8043.

I'm using nifi 1.17.0

@MattWhosorry for pinging you directly, but you were the assignee of the jira ticket and I've seen you commenting other similar posts.

 

Thanks

9 replies

MattWho
Titanium ⭐️⭐️⭐️
MattWho
Titanium ⭐️⭐️⭐️
October 21, 2022

@DGaboleiro 
That is not me as the assignee to jira https://issues.apache.org/jira/browse/NIFI-8043.  But that Matt is an awesome guy @mburgess.

Thanks,

Matt

Translate
    DGaboleiro
    DGaboleiroAuthor
    Silver ⭐️⭐️
    December 13, 2022

    Hey @mburgess could you give a little help in this ticket? I am still facing this issue and I'm not sure how to implement a workaroud

    Translate
    DGaboleiro
    DGaboleiroAuthor
    Silver ⭐️⭐️
    December 19, 2022

    Update: Created a custom processor by duplicating PutDatabaseRecord and added the if statement that was done in the commit https://github.com/apache/nifi/pull/4772/commits/0d6bca493842286ddae6a0577dfed55cd1808f7c. It solved the issue but I had to create a NAR with all of the bundled dependencies in nifi-standard-processors because I was having an error while starting NiFi (https://community.cloudera.com/t5/Support-Questions/Nifi-throwing-error-on-startup/m-p/240893)

    I've checked the code base and the if statement is not present in Nifi > 1.12

     

    Hey @MattWho, since you were the only one helping and @mburgess is not replying to the ticket, do you think it is possible/doable to create an hot-fix or something like that to have this commit? - I've seen more people complaining regarding this issue and it would be useful to not duplicate the source code just to add an if statement

     

    Thanks

     

    Translate
    mburgess
    Gold ⭐️⭐️⭐️
    mburgess
    Gold ⭐️⭐️⭐️
    December 20, 2022

    I wasn't able to reproduce this, I remember trying your example and the UPSERT worked for me, so I'm not sure what's going on

    Translate
    LevinYI
    LevinYI
    Silver ⭐️
    May 16, 2023

    Hello, @mburgess I'm facing same issue with upsert to Postgresql.
    My software versions:
    NiFi version 1.19.1, Postgresql 13, JDBC driver: postgresql-42.5.1.jar

    When I started to make sample flow to represent this bug, I found another one similar 🙂

    I created two tables with camelcase in table and colum names: MyTable and MyTable2 with similar columns list using queries:

    Spoiler

    CREATE TABLE IF NOT EXISTS
    public."MyTable" ("Id" SERIAL PRIMARY KEY, "Col1" VARCHAR(1024), "createdAt" TIMESTAMP DEFAULT NOW());

    CREATE TABLE IF NOT EXISTS
    public."MyTable2" ("Id" SERIAL PRIMARY KEY, "Col1" VARCHAR(1024), "createdAt" TIMESTAMP DEFAULT NOW());

     

    First, I've generated JSON and tried to put some rows in table MyTable using ConvertJSONToSQL. Here is my ConvertJSONToSQL processor:

    JSONToSQL.png
    And here I got a "new" bug. ConvertJSONToSQL quotes schema name along with table name. As result I get "public.MyTable" in constructed query, and Postgres rejects it.

    Spoiler
    INSERT INTO "public.MyTable" ("Col1") VALUES (?)

     

    Okay, I've inserted some rows manually in MyTable and tried to clone data using ExecuteSQL processor connected directly to PutDatabaseRecord.
    ExecuteSql processor:

    ExecuteSQL.png

    PutDatabaseRecord processor:

    PutDatabaseRecord1.png

    PutDatabaseRecord2.png

    PutDatabaseRecord generates correct quoting on "public"."Mytable" but does not quote key column indentifier in ON CONFLINCT clause as topic starter pointed.

    Spoiler
    INSERT INTO "public"."MyTable2"("Id", "Col1", "createdAt")
    VALUES (1, 'value1', '2023-05-16 08:10:56+03')
    ON CONFLICT (ID)
    DO UPDATE SET ("Id", "Col1", "createdAt") =
    (EXCLUDED."Id", EXCLUDED."Col1", EXCLUDED."createdAt")

    I hope this helps to figure out how to reproduce this bug.
    UPD: PutDatabaseRecord also changes key colum name to uppercase.

    Translate
    Vasu_
    Vasu_
    Silver ⭐️⭐️⭐️
    June 13, 2023

    @mburgess I have the same issues with Version 1.18.0 and also with 1.21.0, Do we have any solution on this one, this is breaking the upsert operation for Postgresql.

    Translate
    mburgess
    Gold ⭐️⭐️⭐️
    mburgess
    Gold ⭐️⭐️⭐️
    June 13, 2023

    I was finally able to reproduce the issue, looking into a fix now

    Translate
    mburgess
    Gold ⭐️⭐️⭐️
    mburgess
    Gold ⭐️⭐️⭐️
    June 13, 2023

    It looks like this is a regression from https://issues.apache.org/jira/browse/NIFI-9607 but will need a different solution than just restoring the code changes as it would fail the behavior the aforementioned Jira was meant to fix.

    Translate
      mburgess
      Gold ⭐️⭐️⭐️
      mburgess
      Gold ⭐️⭐️⭐️
      June 13, 2023

      Tracking here: https://issues.apache.org/jira/browse/NIFI-11682

      Translate
        Terms & ConditionsAccessibility statement