JPA offset storage fails on PostgreSQL

[TimMoore]

Lagom Version

1.3.0-SNAPSHOT

API

Java

Expected Behavior

1. Clone https://github.com/TimMoore/activator-lagom-java-chirper-jpa.git and check out jpa-example-postgresql-hibernate
2. sbt runAll
3. Try to chirp something
4. Reload the page
5. You should see the new chirp

Actual Behavior

The new chirp does not appear after reloading the page.

This error is logged on the console:

[error] a.a.OneForOneStrategy - org.hibernate.QueryException: Expected positional parameter count: 10, actually detected 4
java.util.concurrent.CompletionException: org.hibernate.QueryException: Expected positional parameter count: 10, actually detected 4
	at java.util.concurrent.CompletableFuture.encodeRelay(CompletableFuture.java:326) ~[na:1.8.0_112]
	at java.util.concurrent.CompletableFuture.completeRelay(CompletableFuture.java:338) ~[na:1.8.0_112]
	at java.util.concurrent.CompletableFuture.uniRelay(CompletableFuture.java:911) ~[na:1.8.0_112]
	at java.util.concurrent.CompletableFuture$UniRelay.tryFire(CompletableFuture.java:899) ~[na:1.8.0_112]
	at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:474) ~[na:1.8.0_112]
	at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1977) ~[na:1.8.0_112]
	at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:21) ~[scala-java8-compat_2.11-0.7.0.jar:na]
	at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:18) ~[scala-java8-compat_2.11-0.7.0.jar:na]
	at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:32) ~[scala-library-2.11.8.jar:na]
	at scala.concurrent.BatchingExecutor$Batch$$anonfun$run$1.processBatch$1(BatchingExecutor.scala:63) ~[scala-library-2.11.8.jar:na]
Caused by: org.hibernate.QueryException: Expected positional parameter count: 10, actually detected 4
	at org.hibernate.query.internal.QueryParameterBindingsImpl.verifyParametersBound(QueryParameterBindingsImpl.java:270) ~[hibernate-core-5.2.5.Final.jar:5.2.5.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.beforeQuery(AbstractProducedQuery.java:1307) ~[hibernate-core-5.2.5.Final.jar:5.2.5.Final]
	at org.hibernate.query.internal.NativeQueryImpl.beforeQuery(NativeQueryImpl.java:223) ~[hibernate-core-5.2.5.Final.jar:5.2.5.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1483) ~[hibernate-core-5.2.5.Final.jar:5.2.5.Final]
	at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.updateOffset(JpaReadSideImpl.java:178) ~[lagom-javadsl-persistence-jpa_2.11.jar:1.3.0-SNAPSHOT]
	at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.lambda$null$3(JpaReadSideImpl.java:139) ~[lagom-javadsl-persistence-jpa_2.11.jar:1.3.0-SNAPSHOT]
	at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$withTransaction$0(JpaSessionImpl.java:64) ~[lagom-javadsl-persistence-jpa_2.11.jar:1.3.0-SNAPSHOT]
	at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$null$cb7af80$1(JpaSessionImpl.java:103) ~[lagom-javadsl-persistence-jpa_2.11.jar:1.3.0-SNAPSHOT]
	at play.utils.Threads$.withContextClassLoader(Threads.scala:21) ~[play_2.11-2.5.10.jar:2.5.10]
	at play.utils.Threads.withContextClassLoader(Threads.scala) ~[play_2.11-2.5.10.jar:2.5.10]

Explanation

Slick's PostgreSQL driver does not support an atomic insertOrUpdate operation:

InsertOrUpdate operations are emulated on the server side with a single JDBC statement executing multiple server-side statements in a transaction.

This means that the naive attempt to bind parameters to the offset update query in JpaReadSideImpl.java is not database-independent.

[ignasi35]

Apparently Slick doesn't support insertOrUpdate for Postgre by default (http://slick.lightbend.com/doc/3.1.1/api/index.html#slick.driver.PostgresDriver) but it's possible to have it up and running with tminglei's Slick-PG extensions.

UPSERT was only added in PG in the recent 9.5 so it's possible that tminglei's insertOrUpdate would still fail in PG pre-9.5.

@TimMoore what PG version did you use?

[TimMoore]

@ignasi35 I was using pg 9.6, but I think it's better to stick with what Slick supports by default. It emulates upsert with a compound statement, and I think that's totally fine aside from the fact that it changes the statement bind parameters.

@jroper made the brilliantly simple suggestion of matching on the Slick driver class and dispatching to different binding logic (similar to what SlickProvider already does). My brain just doesn't even see these kinds of "dirty" approaches 😉 but it's probably the most pragmatic approach.

I'll also follow up with Stefan to see if there's a cleaner way to do this sort of thing, or whether he'd be receptive to a pull request that adds one. It would be nice if Slick could decouple logic for building and binding statements from actually running them, but as far as I can tell, there is no support for this currently.

[TimMoore]

Fixed for 1.3.0-RC2