Uploaded image for project: 'Apache NiFi'
  1. Apache NiFi
  2. NIFI-8530

PutDatabaseRecord DELETE issue with Postgres UUID



    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.13.2
    • 1.14.0
    • Core Framework
    • None


      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 =
                      "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 */);

      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.




        1. image-2021-05-11-10-46-57-985.png
          123 kB
          Dries Van Autreve

        Issue Links



              mattyb149 Matt Burgess
              driesva Dries Van Autreve
              0 Vote for this issue
              3 Start watching this issue



                Time Tracking

                  Original Estimate - Not Specified
                  Not Specified
                  Remaining Estimate - 0h
                  Time Spent - 1h 20m
                  1h 20m