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:
I can easily reproduce this standalone with following sample (table customer with primary key id of type uuid):
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.