You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "ASF subversion and git services (Jira)" <ji...@apache.org> on 2021/07/07 19:53:00 UTC
[jira] [Commented] (NIFI-8530) PutDatabaseRecord DELETE issue with
Postgres UUID
[ https://issues.apache.org/jira/browse/NIFI-8530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17376810#comment-17376810 ]
ASF subversion and git services commented on NIFI-8530:
-------------------------------------------------------
Commit e16016b4abc771f9e4d62ec9d8a0e626ea9dba08 in nifi's branch refs/heads/main from Matt Burgess
[ https://gitbox.apache.org/repos/asf?p=nifi.git;h=e16016b ]
NIFI-8530: Improved DELETE handling in PutDatabaseRecord for non-nullable columns
This closes #5173
Signed-off-by: David Handermann <ex...@apache.org>
> 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
> Assignee: Matt Burgess
> Priority: Major
> Attachments: image-2021-05-11-10-46-57-985.png
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> 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-46-57-985.png|width=440,height=122!
> 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)