Requests fail when executed transactionally in parallel

[nikitacometa]

Hi, we're using finagle-postgres for our project, it's great, but we've faced an unpleasant bug.

For example we have table:

CREATE TABLE dummy (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

And we want to insert several values in transactional manner:

  it should "work" in {
    val ids = Seq(1, 2)
    val transaction = dbClient.inTransaction { client =>
      Future.collect {
        ids.map { id =>
          client.prepareAndExecute("INSERT INTO dummy (id) VALUES ($1)", id)
        }
      }
    }
    Await.result(transaction)
  }

But instead we're getting an error:
com.twitter.finagle.postgres.codec.ServerError: SQLSTATE 23505: duplicate key value (id)=(2) violates unique constraint "primary"

Enabling LoggingFilter on the client gives such logs:

Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Query(SELECT * FROM pg_catalog.pg_tables WHERE tableowner = 'root')
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Query(BEGIN)
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Parse(,INSERT INTO dummy (id) VALUES ($1),ArraySeq(23))
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Parse(,INSERT INTO dummy (id) VALUES ($1),ArraySeq(23))
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: BIND[0000000131]
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: BIND[0000000132]
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Describe(true,)
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Describe(true,)
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter log
INFO: Execute(,0)
Jan 21, 2021 1:31:14 PM com.twitter.finagle.filter.LoggingFilter logException
INFO: VERY BAD
com.twitter.finagle.postgres.codec.ServerError: SQLSTATE 23505: duplicate key value (id)=(2) violates unique constraint "primary"

So we can see that sending parameters is different, but somehow it tries to insert the same value twice.

It works fine if we use Future.traverseSequentially instead of Future.collect, but it's not what we want.

Same error pops when we use transactional parallel SELECT requests with client.queryAs.

We use CockroachDB but it behave the same on PostgreSQL. For testing we use "com.dimafeng:testcontainers-scala" but same thing reproduces with instance in docker-compose.
Client config:

  val client: PostgresClient = Postgres.Client()
    .withCredentials(config.user, config.password)
    .database(config.database)
    .filtered(loggingFilter)
    .newRichClient(config.host)

.withBinaryParams doesn't help.

[nikitacometa]

I've realized that it's not really a bug but feature, because transaction goes through a single connection and Postgres doesn't support concurrent requests for a single connection which implies that driver also wouldn't try to do that.
But I'm still interested is it possible to handle requests more efficient than with Future.traverseSequentially?