Invalid return value on invalid query

[macobo]

  val query = sql"BEGIN; SELECT 'foobar'; COMMIT".as[String]
  val r: Vector[String] = run(query) // db.run(query)
  println(r, r(0).isInstanceOf[String], r(0).isInstanceOf[Integer])

Outputs

(Vector(0),false,true)

Relevant debug log:

2016-08-24 22:33:43 DEBUG [] statement:40 - Preparing statement: BEGIN; SELECT 'foobar'; COMMIT
2016-08-24 22:33:43 DEBUG [] benchmark:40 - Execution of prepared statement took 1ms
2016-08-24 22:33:43 DEBUG [] result:40 - 0 rows affected

Note how the result seemingly escapes the type system.

[macobo]

To show that this is actually relevant: we are also encountering this 'wrong value returned' problem in production under a different condition under some rare race condition we have yet to track down.

Details about setup: we run postgresql 9.4/9.5, connect to it using latest pgbouncer in transaction pooling mode[1].

In some rare conditions, this same error occurs - the database returns 0 or -1 instead of a proper value. We upsert [2] events to many (>50) database machines on short-lived connections via slick by invoking a pgsql function on the machine which returns a value which we then use down the line in slick. I'd estimate the error to occur once in every 100000 events, but it varies.

In addition, we've linked the issue to another major one. Say we are upserting event A which should return value Va but instead returned 0. A few log lines later we see that upserting event B we get back the value Va instead of the expected Vb. In short, we get the wrong value from the database!

We've managed to "beat" this by forcing all of our queries to take place .transactionally (which I'd have assumed happens by default, but guess not). The database still returns 0 or -1 occasionally, but it never returns results from another query.

[1] Session pooling mode nor connecting to pg directly inhibit this behavior. Neither of these are not suited for our needs where we need many short-lived connections from many more clients than a single database can handle while fielding work_mem-hungry long-lived reads as well.

[2] Query is of form sql"SELECT upsert_foo($a, $b, ...);".as[ReturnType]

[cvogt]

this looks very weird. have you tried the same using just plain old jdbc? What is the result there?

[klpx]

Hi! I have same issue with postgres 9.4. I thought that cause in pgpool2, do you use it?
And which driver version you use?

[szeiger]

There is no way the GetResult could go wrong. This fails because the statement doesn't return a ResultSet, so GetResult is never used. Instead you get the row count: https://github.com/slick/slick/blob/master/slick/src/main/scala/slick/jdbc/StatementInvoker.scala#L72-L75

The only solution I can think of is to add a separate method to GetResult that needs to be called for reading an update count (or something else? Maybe we could use it to read inserted keys, too!). All implicit implementations other than GetInt would throw a ClassCastException.

[macobo]

This was the symptom of a rather insidious bug at where I worked.

Namely due to a race condition in pgbouncer query cancellation logic, occasionally the wrong query would get cancelled and the returned resultset would be empty and occasionally we'd get the wrong resultset back. pgbouncer/pgbouncer#141

This issue caused us to suspect slick for a while and is really surprising behavior imo. I don't have a clear idea on how to fix this though.