[
https://issues.apache.org/jira/browse/NIFI-10685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nathan Gough reassigned NIFI-10685:
-----------------------------------
Assignee: Nathan Gough
> PutDatabaseRecord Upsert does not quote columns in conflict clause (postgres)
> -----------------------------------------------------------------------------
>
> Key: NIFI-10685
> URL: https://issues.apache.org/jira/browse/NIFI-10685
> Project: Apache NiFi
> Issue Type: Bug
> Components: Extensions
> Affects Versions: 1.18.0
> Reporter: mgerbig
> Assignee: Nathan Gough
> Priority: Major
> Attachments: putdatabase-test.avro
>
>
> Nifi's PutDatabaseRecord does not quote columns in the conflict clause when
> using PostgreSQLDatabaseAdapter. This prevents the usage of upsert (and
> possibly insert ignore) statements as soon as at least one column of the
> primary key contains at least one uppercase character.
> h2. Root Cause
> Postgres lowercases all unquoted keys, as described in the manual, which
> causes this bug:
> {quote}Quoting an identifier also makes it case-sensitive, whereas unquoted
> names are always folded to lower case. For example, the identifiers
> {{{}FOO{}}}, {{{}foo{}}}, and {{"foo"}} are considered the same by
> PostgreSQL, but {{"Foo"}} and {{"FOO"}} are different from these three and
> each other. (The folding of unquoted names to lower case in PostgreSQL is
> incompatible with the SQL standard, which says that unquoted names should be
> folded to upper case. Thus, {{foo}} should be equivalent to {{"FOO"}} not
> {{"foo"}} according to the standard. If you want to write portable
> applications you are advised to always quote a particular name or never quote
> it.)
> {quote}
> see
> [https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS]
> h2. Reproduce behaviour
> Create a table in your *PostgreSQL* database
> {code:java}
> CREATE TABLE test_table
> (
> "TIME_PERIOD" date NOT NULL,
> "R0102" numeric,
> "R0203" numeric,
> "R0304" numeric,
> "R0405" numeric,
> "R0506" numeric,
> "R0607" numeric,
> "R0708" numeric,
> "R0809" numeric,
> "R0910" numeric,
> modified_at_utc date DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP(0)),
> CONSTRAINT test_table PRIMARY KEY ("TIME_PERIOD")
> ) {code}
> Use attached avro to write to the table using a PutDatabaseRecord Processor
> configured with UPSERT.
> Nifi will throw following Exception in the Bulletin Board
> {code:java}
> PutDatabaseRecord[id=...] Failed to put Records to database for
> FlowFile[filename=redacted.avro]. Routing to failure.:
> java.sql.BatchUpdateException: Batch entry 0 INSERT INTO
> "public"."test_table"("TIME_PERIOD", "R0102", "R0203", "R0304", "R0405",
> "R0506", "R0607", "R0708", "R0809", "R0910") VALUES ('2022-10-21 +00',
> '2.97'::numeric, '3.2'::numeric, '3.19'::numeric, '3.19'::numeric,
> '3.22'::numeric, '3.33'::numeric, '3.33'::numeric, '3.4'::numeric,
> '3.38'::numeric) ON CONFLICT (TIME_PERIOD) DO UPDATE SET ("TIME_PERIOD",
> "R0102", "R0203", "R0304", "R0405", "R0506", "R0607", "R0708", "R0809",
> "R0910") = (EXCLUDED."TIME_PERIOD", EXCLUDED."R0102", EXCLUDED."R0203",
> EXCLUDED."R0304", EXCLUDED."R0405", EXCLUDED."R0506", EXCLUDED."R0607",
> EXCLUDED."R0708", EXCLUDED."R0809", EXCLUDED."R0910") was aborted: ERROR:
> column "time_period" does not exist
> Position: 347 Call getNextException to see other errors in the batch.
> - Caused by: org.postgresql.util.PSQLException: ERROR: column "time_period"
> does not exist
> Position: 347 {code}
> h3. Generated SQL Statement
> Nifi generates and issues following SQL Statement to the database:
> {code:java}
> INSERT INTO "public"."test_table"("TIME_PERIOD", "R0102", "R0203", "R0304",
> "R0405", "R0506", "R0607", "R0708", "R0809", "R0910") VALUES (?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?) ON CONFLICT (TIME_PERIOD) DO UPDATE SET ("TIME_PERIOD",
> "R0102", "R0203", "R0304", "R0405", "R0506", "R0607", "R0708", "R0809",
> "R0910") = (EXCLUDED."TIME_PERIOD", EXCLUDED."R0102", EXCLUDED."R0203",
> EXCLUDED."R0304", EXCLUDED."R0405", EXCLUDED."R0506", EXCLUDED."R0607",
> EXCLUDED."R0708", EXCLUDED."R0809", EXCLUDED."R0910") {code}
> This statement only works with quoted _TIME_PERIOD_ in the _ON CONFLICT_
> clause due to the behaviour of postgres described in Section _Root Cause_
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
