[jira] [Comment Edited] (DRILL-7459) Fetch size does not work on Postgres JDBC plugin

["Arina Ielchiieva (Jira)" <ji...@apache.org>]

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

Arina Ielchiieva edited comment on DRILL-7459 at 3/16/20, 4:15 PM:
-------------------------------------------------------------------

Was fixed in the scope of DRILL-7467.
The following plugin configuration should be used to support batches:
{noformat}
{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://localhost:5959/my_db?defaultRowFetchSize=2",
  "username": "my_user",
  "password": "my_pass",
  "caseInsensitiveTableNames": false,
  "sourceParameters": {
    "defaultAutoCommit": false
  },
  "enabled": true
}
{noformat}

*NOTE*
In DRILL-7639 BasicDataSource was replaced with Hikari, {{sourceParameters}} sections remains and allows to configure data source, Hikari related configuration properties should be used instead. List of Hikari properties: https://github.com/brettwooldridge/HikariCP.

Updated configuration will be the following:
{noformat}
{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://localhost:5959/my_db?defaultRowFetchSize=2",
  "username": "my_user",
  "password": "my_pass",
  "caseInsensitiveTableNames": false,
  "sourceParameters": {
    "autoCommit": false
  },
  "enabled": true
}
{noformat}

{{defaultAutoCommit}} was replaced with {{autoCommit}}.


was (Author: arina):
Was fixed in the scope of DRILL-7467.
The following plugin configuration should be used to support batches:
{noformat}
{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://localhost:5959/my_db?defaultRowFetchSize=2",
  "username": "my_user",
  "password": "my_pass",
  "caseInsensitiveTableNames": false,
  "sourceParameters": {
    "defaultAutoCommit": false
  },
  "enabled": true
}
{noformat}

> Fetch size does not work on Postgres JDBC plugin
> ------------------------------------------------
>
>                 Key: DRILL-7459
>                 URL: https://issues.apache.org/jira/browse/DRILL-7459
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Storage - JDBC
>    Affects Versions: 1.15.0
>            Reporter: Priyanka Bhoir
>            Assignee: Arina Ielchiieva
>            Priority: Major
>             Fix For: 1.18.0
>
>
> To prevent the drill from going out of memory, it is suggested to set the fetch size in Jdbc URL to enable data streaming([#DRILL-6794 | https://issues.apache.org/jira/browse/DRILL-6794] discusses this). This does not work on Postgres for the following reason:
> For fetchSize size to work on Postgres, the connection must not be in autocommit mode. There is no parameter to set autocommit to false in connection string other than programmatically calling conn.setAutoCommit(false).
> See [https://jdbc.postgresql.org/documentation/93/query.html#fetchsize-example]
> See [https://jdbc.postgresql.org/documentation/head/connect.html] for the list of all connection string properties.
> Fix is to add a property 'defaultAutoCommit' to JdbcStorageConfig and call BasicDataSource#setDefaultAutoCommit in JdbcStoragePlugin.



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