How to handle PostgreSQL timeout in groovy

Question

In my Groovy script, I have following structure:

def sql = Sql.newInstance(connString, "user", "password", 
"org.postgresql.Driver")

sql.withTransaction {
     sql.withBatch(){}

     sql.withBatch(){}

     sql.withBatch(){}

     .........

}

sql.close()

I want to take care of timeout issues here.

But Sql API doesn't have any method for it.

So how can I do it? I am using PostgreSQL driver.

I came across this. But I get error:

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4Connection.setNetworkTimeout(Executor, int) is not yet implemented.

PS:

int[] modifyCount = sql.withBatch(batchSize, updateQuery) { ps ->
        keyValue.each { k,v ->
            ps.addBatch(keyvalue:k, newvalue:v)
        }
    }

In above code, when I try to add ps.setQueryTimeout(), error message says no such method defined.

score 2 · Accepted Answer · answered May 13 '19 at 09:12

2

Low-level timeouts could be defined through connection properties:

https://jdbc.postgresql.org/documentation/head/connect.html

loginTimeout Specify how long to wait for establishment of a database connection.
connectTimeout The timeout value used for socket connect operations.
socketTimeout The timeout value used for socket read operations.

These properties may be specified in either the connection URL or an additional Properties object parameter.

Query timeout. After connecting to the database you could define closure to be executed for each statement:

sql.withStatement{java.sql.Statement stmt->
    stmt.setQueryTimeout( MY_SQL_TIMEOUT )
}

answered May 13 '19 at 09:12

daggett

26,404
3
40
56

inside withBatch you have [wrapper](http://docs.groovy-lang.org/latest/html/api/groovy/sql/BatchingPreparedStatementWrapper.html) and it does not have full access to a statement functions. try exactly `sql.withStatement` just after connection established and before any other sql operations. – daggett May 13 '19 at 10:37
you could try `sql.withBatch{ps-> ps.delegate.setQueryTimeout(...) }` but this will use access to a private member `delegate`, and it's possible that will not work in future versions. – daggett May 13 '19 at 10:40
second approach gives java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int) is not yet implemented. – Mandroid May 13 '19 at 11:09
So, postgre driver does not support this. (( – daggett May 13 '19 at 11:39

How to handle PostgreSQL timeout in groovy

1 Answers1