[jira] [Commented] (NIFI-5724) Make the autocommit value in the PutSQL processor configurable

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-5724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16671210#comment-16671210 ] 

ASF GitHub Bot commented on NIFI-5724:
--------------------------------------

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.


> Make the autocommit value in the PutSQL processor configurable
> --------------------------------------------------------------
>
>                 Key: NIFI-5724
>                 URL: https://issues.apache.org/jira/browse/NIFI-5724
>             Project: Apache NiFi
>          Issue Type: Improvement
>          Components: Extensions
>            Reporter: vish uma
>            Priority: Minor
>
> The PutSQL processor currently always sets the autocommit value on the database session to false before the SQL statement is run and resets it back to the original value after. 
> i am not sure if the autocommit value is hardcoded to false for a reason, if it is, please let me know.
> This is causing an issue with the snowflake DB where abruptly disconnected sessions do not release the locks they have taken.
> i would like to make this autocommit value configurable. I can submit a patch for this if there is no objections.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)