# Configure logstash input jdbc plugin to disable autocommit for postgresql connection

**URL:** <https://discuss.elastic.co/t/configure-logstash-input-jdbc-plugin-to-disable-autocommit-for-postgresql-connection/206307>\
**Category:** Logstash\
**Created:** [November 3, 2019, 11:02pm UTC](https://discuss.elastic.co/t/configure-logstash-input-jdbc-plugin-to-disable-autocommit-for-postgresql-connection/206307 "2019-11-03T23:02:55Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![turntwo463](https://avatars.discourse-cdn.com/v4/letter/t/5f9b8f/32.png) [@turntwo463](https://discuss.elastic.co/u/turntwo463)\
**Post date:** [November 3, 2019, 11:02pm UTC](https://discuss.elastic.co/t/configure-logstash-input-jdbc-plugin-to-disable-autocommit-for-postgresql-connection/206307/1 "2019-11-03T23:02:55Z")

</div>

I've come across the issue where the fetch size cannot be applied for postgresql jdbc connections since the connections default to autocommit, as reported here:

> <https://github.com/logstash-plugins/logstash-input-jdbc/issues/103>
>
> logstash 1.5.5
> jdbc 1.0.0
> error log:
> java.lang.OutOfMemoryError: Java heap space
> Dumping heap to java\_pid24312.hprof ...
> Heap dump file created \[1159466364 bytes in 21.477 secs\]
> i read
> https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc\_page\_size
> add parameter...

Looking at the documentation I see that under the sequel\_opts there is an option to specify an after\_connect callable object.

I'm trying to test whether implementing after\_connect to disable autocommit will then allow the fetch size to be honored. Or perhaps the after\_connect needs to disable autocommit and then reapply the fetch size value.

Does this seems like an approach that may work, or has anyone already gone down this path and seen it fail?

I'm trying to implement this inline within my logstash configuration file. I've been unable to come up with valid Ruby syntax to have the script correctly called.

I've tried several variations -- see below for my latest attempt. I would appreciate any guidance on how to correctly represent this code.

```
indent preformatted text by 4 spaces
jdbc {
    # Postgres jdbc connection string to our database
    jdbc_connection_string => "${JDBC_URL}"
	
    # validate connection before use
    jdbc_validate_connection => true
    
    # The user we wish to execute our statement as
    jdbc_user => "${JDBC_USER}"
	
    # The password for jdbc_user
    jdbc_password => "${JDBC_PWRD}"
			
    # The path to our downloaded jdbc driver
    jdbc_driver_library => "${JDBC_JAR}"
	
  
    # The name of the driver class for Postgresql
    jdbc_driver_class => "${JDBC_DRIVER}"
	
      
    sql_log_level => "debug"
    jdbc_fetch_size => 10000
	
    sequel_opts => {
	after_connect =>
		"proc { |c| c.autocommit = false } end"				
    }
		
	
	
# our query
    statement => "some valid select statement"   

```

}

Thank you.  
David

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 1, 2019, 11:03pm UTC](https://discuss.elastic.co/t/configure-logstash-input-jdbc-plugin-to-disable-autocommit-for-postgresql-connection/206307/2 "2019-12-01T23:03:01Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.