Error when executing query with jasync-sql: "Can't run query because there is one query pending already"

Question

We're currently using jasync-sql PostgreSQL async library in a Ktor service and we're getting:

14:02:20.005 [DefaultDispatcher-worker-1] ERROR com.github.jasync.sql.db.postgresql.PostgreSQLConnection - Can't run query because there is one query pending already
14:02:20.008 [nettyCallPool-4-1] ERROR Application - Unhandled: GET - /api/customer_book_list
com.github.jasync.sql.db.exceptions.ConnectionStillRunningQueryException: <1> - There is a query still being run here - race -> false
        at com.github.jasync.sql.db.postgresql.PostgreSQLConnection.notReadyForQueryError(PostgreSQLConnection.kt:297)
        at com.github.jasync.sql.db.postgresql.PostgreSQLConnection.validateIfItIsReadyForQuery(PostgreSQLConnection.kt:305)
        at com.github.jasync.sql.db.postgresql.PostgreSQLConnection.validateQuery(PostgreSQLConnection.kt:312)
        at com.github.jasync.sql.db.postgresql.PostgreSQLConnection.sendPreparedStatement(PostgreSQLConnection.kt:133)
        at util.PostgresClient$sendPreparedStatement$2.doResume(PostgresClient.kt:19)
        at kotlin.coroutines.experimental.jvm.internal.CoroutineImpl.resume(CoroutineImpl.kt:42)
        at kotlinx.coroutines.experimental.DispatchedTask$DefaultImpls.run(Dispatched.kt:168)
        at kotlinx.coroutines.experimental.DispatchedContinuation.run(Dispatched.kt:13)
        at kotlinx.coroutines.experimental.scheduling.Task.run(Tasks.kt:94)
        at kotlinx.coroutines.experimental.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:567)
        at kotlinx.coroutines.experimental.scheduling.CoroutineScheduler.access$runSafely(CoroutineScheduler.kt:60)
        at kotlinx.coroutines.experimental.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:713)

Do we have to open a connection for each request? Or maybe the configuration is wrong?

Answer 1

In jasync-sql each Connection can handle one statement execution at a time. For multiple connections the best approach is using a ConnectionPool like this:

PoolConfiguration poolConfiguration = new PoolConfiguration(
        100,                            // maxObjects
        TimeUnit.MINUTES.toMillis(15),  // maxIdle
        10_000,                         // maxQueueSize
        TimeUnit.SECONDS.toMillis(30)   // validationInterval
);
Connection connectionPool = new ConnectionPool<>(
                                  new PostgreSQLConnectionFactory (configuration), poolConfiguration);

Connection itself do not have a buffer or queue for pending requests, so unless you want to create a connection per request which is inefficient, a connection pool should be used.

A complete Kotlin example can be found here: https://github.com/jasync-sql/jasync-sql/blob/master/samples/ktor/src/application.kt