exec(String) throws SQLException: ResultSet already requested

[jeiea]

I wrote the following code and was stuck in exec(query). The backend was sqlite.

private fun Transaction.getTables(): MutableList<String> {
  val sqliteQuery = "SELECT name FROM sqlite_master WHERE type='table'"
  val postgreQuery = "SELECT table_name FROM information_schema.tables WHERE table_schema='public';"
  val query = if (isSqlite) sqliteQuery else postgreQuery
  return execStringColumn(query)
}

private fun Transaction.execStringColumn(query: String): MutableList<String> {
  val fields = mutableListOf<String>()
  exec(query) { cursor ->
    while (cursor.next()) {
      fields.add(cursor.getString(0))
    }
  }
  return fields
}

The above code throws the following exception.

INFO Exposed - Transaction attempt #2 failed: java.sql.SQLException: ResultSet already requested. Statement(s): SELECT name FROM sqlite_master WHERE type='table'
org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLException: ResultSet already requested
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:61)
...

And followings are stacktraces of each call to getResultSet.

getResultSet:151, JDBC3Statement (org.sqlite.jdbc3)
executeQuery:85, JDBC3PreparedStatement (org.sqlite.jdbc3)
executeQuery:52, ProxyPreparedStatement (com.zaxxer.hikari.pool)
executeQuery:-1, HikariProxyPreparedStatement (com.zaxxer.hikari.pool)
executeInternal:104, Transaction$exec$2 (org.jetbrains.exposed.sql)
executeIn$exposed:59, Statement (org.jetbrains.exposed.sql.statements)
exec:128, Transaction (org.jetbrains.exposed.sql)
exec:122, Transaction (org.jetbrains.exposed.sql)
exec:101, Transaction (org.jetbrains.exposed.sql)

getResultSet:133, JDBC3Statement (org.sqlite.jdbc3)
getResultSet:214, ProxyStatement (com.zaxxer.hikari.pool)
getResultSet:-1, HikariProxyPreparedStatement (com.zaxxer.hikari.pool)
executeInternal:107, Transaction$exec$2 (org.jetbrains.exposed.sql)
executeIn$exposed:59, Statement (org.jetbrains.exposed.sql.statements)
exec:128, Transaction (org.jetbrains.exposed.sql)
exec:122, Transaction (org.jetbrains.exposed.sql)
exec:101, Transaction (org.jetbrains.exposed.sql)

Did I have any mistake on executing raw SQL?

[Tapac]

The one thing that comes in mind is that you share transaction between threads and access connection concurrently. Or maybe you run SQLite in single-thread mode (https://www.sqlite.org/threadsafe.html) ?

Does the same exception happen on PSQL?

[jeiea]

I don't know how to check SQLite threading mode, but I set TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE and other DSL/DAO api works well.
With postgres other kind of exception happens, maybe due to incorrect implementation. In other words, not happens.

[Tapac]

@jeiea , could you provide a reproducible sample for that issue?

[jeiea]

exposed-sqlite.zip
@Tapac Here it is.

[Tapac]

Thank you, your sample helps a lot. Fixed in a master branch.

[riyadparvez]

I am seeing the same issue in postgres 9.6.

[Tapac]

@riyadparvez , on the latest Exposed version?

[riyadparvez]

Yes I am still seeing this exception version 0.12.2. Here's the full stack-trace

[main] WARN Exposed - Transaction attempt #2 failed: This ResultSet is closed.. Statement(s): null
org.postgresql.util.PSQLException: This ResultSet is closed.
        at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2741)
        at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1829)
        at petals.util.ResultIterator.hasNext(ExposedUtils.kt:19)
        at kotlin.sequences.TransformingSequence$iterator$1.hasNext(Sequences.kt:176)
        at kotlin.sequences.SequencesKt___SequencesKt.toCollection(_Sequences.kt:698)
        at kotlin.sequences.SequencesKt___SequencesKt.toMutableList(_Sequences.kt:728)
        at kotlin.sequences.SequencesKt___SequencesKt.toList(_Sequences.kt:719)
        at petals.FieldWeatherLookup$fieldWeatherLookup$weatherReadings$1.invoke(FieldWeatherLookup.kt:145)
        at petals.FieldWeatherLookup$fieldWeatherLookup$weatherReadings$1.invoke(FieldWeatherLookup.kt:86)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
        at petals.FieldWeatherLookup.fieldWeatherLookup(FieldWeatherLookup.kt:97)
        at petals.PetalsKt.main(petals.kt:51)
Exception in thread "main" org.postgresql.util.PSQLException: This ResultSet is closed.
        at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2741)
        at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1829)
        at petals.util.ResultIterator.hasNext(ExposedUtils.kt:19)
        at kotlin.sequences.TransformingSequence$iterator$1.hasNext(Sequences.kt:176)
        at kotlin.sequences.SequencesKt___SequencesKt.toCollection(_Sequences.kt:698)
        at kotlin.sequences.SequencesKt___SequencesKt.toMutableList(_Sequences.kt:728)
        at kotlin.sequences.SequencesKt___SequencesKt.toList(_Sequences.kt:719)
        at petals.FieldWeatherLookup$fieldWeatherLookup$weatherReadings$1.invoke(FieldWeatherLookup.kt:145)
        at petals.FieldWeatherLookup$fieldWeatherLookup$weatherReadings$1.invoke(FieldWeatherLookup.kt:86)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
        at petals.FieldWeatherLookup.fieldWeatherLookup(FieldWeatherLookup.kt:97)