Dries Van Autreve created NIFI-8530:
---------------------------------------
Summary: 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
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)
