Github user ijokarumawak commented on a diff in the pull request:
https://github.com/apache/nifi/pull/3113#discussion_r229960433
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/PutSQL.java
---
@@ -134,6 +134,14 @@
.expressionLanguageSupported(ExpressionLanguageScope.FLOWFILE_ATTRIBUTES)
.build();
+ static final PropertyDescriptor AUTO_COMMIT = new
PropertyDescriptor.Builder()
+ .name("database-session-autocommit")
+ .displayName("Database session autocommit value")
+ .description("The autocommit mode to set on the database
connection being used.")
+ .allowableValues("true", "false")
+ .defaultValue("false")
+ .build();
--- End diff --
@viswaug That approach may work, too. However, I prefer exposing
auto-commit property and let user to enable it explicitly. And protect existing
logics work as expected by adding following custom validation:
- if auto-commit is enabled:
- `Support Fragmented Transactions` should be false
- && `Rollback On Failure` should be false
This way even if we forgot about other conditions that require auto-commit,
user can disable auto-commit to work-around. PutSQL has been there for long
time and used by so many flows. We can not introduce any degrading issue by
supporting auto-commit.
BTW, the description of Snowflake database issue is not clear enough to me
on how auto-commit setting relates to the issue.
> This is causing an issue with the snowflake DB where abruptly
disconnected sessions do not release the locks they have taken.
Do you have any existing Snowflake issue or resource that we can refer? Or
if not have you consider reporting the issue to Snowflake project?
And also, if you have any use-case (other than avoiding Snowflake issue)
where auto-commit is preferable, please add such to auto-commit property
description.
---
