[
https://issues.apache.org/jira/browse/NIFI-6934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17154672#comment-17154672
]
ASF subversion and git services commented on NIFI-6934:
-------------------------------------------------------
Commit ad95287e782d71a778dfa89b3cb2a17637b32bf5 in nifi's branch
refs/heads/main from Tamas Palfy
[ https://gitbox.apache.org/repos/asf?p=nifi.git;h=ad95287 ]
NIFI-6934 In PutDatabaseRecord added DatabaseAdapter-based UPSERT support for
Postgres (9.5+)
NIFI-6934 Added more documentation and unit tests.
NIFI-6934 Added missing license for new test class.
Signed-off-by: Matthew Burgess <[email protected]>
This closes #4350
> 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
> Reporter: Sergey Shcherbakov
> Priority: Major
> Time Spent: 3.5h
> Remaining Estimate: 0h
>
> 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 supports a 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)
