[NIFI-8530] PutDatabaseRecord DELETE issue with Postgres UUID

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.13.2
• Fix Version/s: 1.14.0
• Component/s: Core Framework
• Assignee: Matt Burgess
• Reporter: Dries Van Autreve
• Created: 11/May/21 08:46
• Updated: 07/Jul/21 19:53
• Resolved: 07/Jul/21 19:53

Description

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:

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:

 

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)

 

 

I can easily reproduce this standalone with following sample (table customer with primary key id of type uuid):

 

    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();
    }
  }

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.

 

 

Issue Links

• is caused by: NIFI-3742 PutDatabaseRecord can not delete row if it contains null value
• links to: GitHub Pull Request #5173