[jira] [Created] (NIFI-6934) Support Postgres 9.5+ Upsert in the standard PutDatabaseRecord processor

["Sergey Shcherbakov (Jira)" <ji...@apache.org>]

Sergey Shcherbakov created NIFI-6934:
----------------------------------------

             Summary: Support Postgres 9.5+ Upsert in the standard PutDatabaseRecord processor
                 Key: NIFI-6934
                 URL: https://issues.apache.org/jira/browse/NIFI-6934
             Project: Apache NiFi
          Issue Type: Improvement
          Components: Extensions
    Affects Versions: 1.10.0
            Reporter: Sergey Shcherbakov


The PutDatabaseRecord processing is setup for batch record processing for more performant records processing and to be able to use JDBC batch API.

Unfortunately, in this setup, in case if a single record in the batch fails to be inserted into the target SQL database, the entire transaction with the full DML batch gets rolled back.
That is, a single failure (e.g. because of a duplicate existing in the database with the same unique business key constraint) makes using PutDatabaseRecord processor unusable in batch mode.

A common workaround for that is inserting a SplitRecord before the PutDatabaseProcessor. That approach works but obviously has disadvantages (slow performance, higher resource consumption, downstream logic changes, since the number of FlowFiles explodes).

The PostgresSQL starting with version 9.5 support special SQL syntax extension that effectively allows UPSERTS, that is, adding new records in case of no constraint conflicts and updating existing record (or doing nothing) in case if there is a conflict:
[http://www.postgresqltutorial.com/postgresql-upsert/]


Such an Upsert would solve the above mentioned problems of the PutDatabaseRecord processor.
Adding support for such extension looks also fairly trivial in the PutDatabaseRecord processor.



 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)