finagle / finagle/finagle-postgres

Requests fail when executed transactionally in parallel

Open
#226 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Scala
Stars
81
Forks
36
PR merge metrics
No merged PRs in 30d

Description

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.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.