Exposed: Leaking conection with hikari and postgres

Using: exposed 0.12.1, PG 42.2.5, hikari 3.2.0

The way I run transaction:

private fun <T> runWithinTransaction(db: Database, block: () -> T): T {
        return transaction(db) {
            addLogger(Slf4jSqlDebugLogger)
            warnLongQueriesDuration = 2000
            connection.prepareStatement("SET LOCAL statement_timeout TO 1").use {
                it.execute()
            }
            block()
        }
    }

I’ve set it to 1ms by design in test, so I can achieve in shorter time same what happen on prod with longer timeouts.

"DefaultDispatcher-worker-4 @request#72" #34 daemon prio=5 os_prio=31 tid=0x00007fdfd9acb800 nid=0x5e07 runnable [0x0000700011556000]
   java.lang.Thread.State: RUNNABLE
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
        at java.net.SocketInputStream.read(SocketInputStream.java:171)
        at java.net.SocketInputStream.read(SocketInputStream.java:141)
        at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
        at org.postgresql.core.PGStream.receiveChar(PGStream.java:306)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1952)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        - locked <0x000000076f80c298> (a org.postgresql.core.v3.QueryExecutorImpl)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
        at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
        at org.postgresql.jdbc.PgDatabaseMetaData.getSQLKeywords(PgDatabaseMetaData.java:320)
        at org.jetbrains.exposed.sql.Database$keywords$2.invoke(Database.kt:42)
        at org.jetbrains.exposed.sql.Database$keywords$2.invoke(Database.kt:16)
        at kotlin.UnsafeLazyImpl.getValue(Lazy.kt:81)
        at org.jetbrains.exposed.sql.Database.getKeywords(Database.kt)
        at org.jetbrains.exposed.sql.Database.needQuotes(Database.kt:57)
        at org.jetbrains.exposed.sql.Transaction.quoteTokenIfNecessary(Transaction.kt:164)
        at org.jetbrains.exposed.sql.Transaction.quoteIfNecessary$exposed(Transaction.kt:158)
        at org.jetbrains.exposed.sql.Transaction.fullIdentity(Transaction.kt:170)
        at org.jetbrains.exposed.sql.Column.toSQL(Column.kt:32)
        at org.jetbrains.exposed.sql.Query$prepareSQL$$inlined$buildString$lambda$1.invoke(Query.kt:169)
        at org.jetbrains.exposed.sql.Query$prepareSQL$$inlined$buildString$lambda$1.invoke(Query.kt:84)
        at kotlin.text.StringsKt__StringBuilderKt.appendElement(StringBuilder.kt:58)
        at kotlin.collections.CollectionsKt___CollectionsKt.joinTo(_Collections.kt:2274)
        at kotlin.collections.CollectionsKt___CollectionsKt.joinToString(_Collections.kt:2291)
        at kotlin.collections.CollectionsKt___CollectionsKt.joinToString$default(_Collections.kt:2290)
        at org.jetbrains.exposed.sql.Query.prepareSQL(Query.kt:169)
        at org.jetbrains.exposed.sql.Query.arguments(Query.kt:153)
        at org.jetbrains.exposed.sql.Query.arguments(Query.kt:84)
        at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:32)
        at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:131)
        at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:125)
        at org.jetbrains.exposed.sql.Query.iterator(Query.kt:291)

Any suggestion what may cause it?

Additionally, is there any other way to set query timeout ?

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 24 (22 by maintainers)

Most upvoted comments

Unfortunately it’s part of our internal code, cannot share it. I’m using Ktor, which is accepting connections then I run queries within:

    @Trace(async = true)
    private suspend fun <T> dbQuery(db: Database, token: Token, block: () -> T): T {
        token.link()
        return withContext(Dispatchers.IO) {
            runWithinTransaction(db, token, block)
        }
    }

    @Trace(async = true)
    private fun <T> runWithinTransaction(db: Database, token: Token, block: () -> T): T {
        token.link()
        try {
            return transaction(
                    transactionIsolation = TransactionManager.manager.defaultIsolationLevel,
                    repetitionAttempts = 0,
                    db = db
            ) {
                addLogger(Slf4jSqlDebugLogger)
                warnLongQueriesDuration = 2000
                connection.prepareStatement("SET LOCAL statement_timeout TO 60000").use {
                    it.execute()
                }
                block()
            }
        } catch (e: SQLException) {
            val exception: SQLException = when {
                e is ExposedSQLException && e.cause != null && e.cause is SQLException -> e.cause as SQLException
                else -> e
            }

            when {
                exception.message != null && exception.message!!.contains("canceling statement due to statement timeout") ->
                    throw QueryTimeout()
                else -> throw e
            }
        }
    }

spolnik on Feb 14, 2019