[
https://issues.apache.org/jira/browse/NIFI-8530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dries Van Autreve updated NIFI-8530:
------------------------------------
Attachment: image-2021-05-11-10-46-57-985.png
> PutDatabaseRecord DELETE issue with Postgres UUID
> -------------------------------------------------
>
> Key: NIFI-8530
> URL: https://issues.apache.org/jira/browse/NIFI-8530
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 1.13.2
> Reporter: Dries Van Autreve
> Priority: Major
> Attachments: image-2021-05-11-10-46-57-985.png
>
>
> We have a scenario where we delete entities using their primary key in a
> Postgres database. Those primary keys are declared as Postgres UUID type
> ([https://www.postgresql.org/docs/11/datatype-uuid.html])
> In our first attempt to use _PutDatabaseRecord_ we saw that the delete
> statement contains all fields of the schema. A suggestion would be to have
> something like "_Delete Keys_" cfr "_Update Keys_" which would allow deletes
> by primary key.
> Anyway, we found a workaround by using a dedicated schema.
> The real issue is that using the construct introduced by NIFI-3742 results in
> the condition {{(id = ? OR (id is null AND ? is null)).}} This gives the
> following error:
> !image-2021-05-11-10-41-41-187.png!
> For uuid the SQL type is OTHER, whereas the datatype in NiFi is string. It
> turns out that Postgres is unable to guess the datatype for the condition {{?
> is null}}:
>
> {noformat}
> org.postgresql.util.PSQLException: ERROR: could not determine data type of
> parameter $2 at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
> at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
> at
> org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153){noformat}
>
>
> I can easily reproduce this standalone with following sample (table customer
> with primary key id of type uuid):
>
> {code:java}
> try (
> Connection conn = ds.getConnection();
> PreparedStatement ps =
> conn.prepareStatement(
> "DELETE FROM customer WHERE (id = ? OR (id is null AND ? is
> null))")
> ) {
> ps.setObject(1, "e9183640-edd0-11ea-aab0-87d9f494b64e", 1111 /* OTHER
> */);
> ps.setObject(2, "e9183640-edd0-11ea-aab0-87d9f494b64e", 1111 /* OTHER
> */);
> ps.execute();
> }
> }
> {code}
> The first improvement, which would already fix our problem, would be not to
> generate the condition from NIFI-3742 for columns that have a not-null
> constraint. I don’t think it makes sense for these columns.
> _PutDatabaseRecord_ already has the information if a column is declared
> nullable (see _ColumnDescription_).
> We could provide PR for this improvement if needed.
>
> To completely fix the problem, additional changes would be required. I guess
> the problem with SQL type OTHER is that it can be anything uuid, jsonb,
> geometry, …
> For UUID only:
> * if you would (naively) try to parse the string to a uuid (and it succeeds)
> it would work for Postgres. It successfully tested the previous sample with
> {{setObject(2, UUID.fromString("e9183640-edd0-11ea-aab0-87d9f494b64a"), 1111
> /* OTHER */);}}
> Not sure If that works with other databases.
> * another option for uuid is maybe to leverage the avro logical typing
> [http://avro.apache.org/docs/current/spec.html#UUID]
> But maybe there should be some database specific logic based on the chosen
> _DatabaseAdapter_ implementation.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
