Scala's Slick with multiple PK insertOrUpdate() throws exception ERROR: syntax error at end of input

Question

I am using Scala' Slick and PostgreSQL. And I am working well with tables with single PK. Now I need to use a table with multiple PKs:

case class Report(f1: DateTime,
    f2: String,
    f3: Double)

class Reports(tag: Tag) extends Table[Report](tag, "Reports") {
    def f1 = column[DateTime]("f1")
    def f2 = column[String]("f2")
    def f3 = column[Double]("f3")

    def * = (f1, f2, f3) <> (Report.tupled, Report.unapply)
    def pk = primaryKey("pk_report", (f1, f2))
}

val reports = TableQuery[Reports]

when I have empty table and use reports.insert(report) it works well. But when I use reports.insertOrUpdate(report) I receive and exception:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 76
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at ....

What am I doing wrong? How to fix it?

Thanks in advance.

---

PS. I tried workaround - tried to implement "if exist update else insert" logic by:

  val len = reports.withFilter(_.f1 === report.f1).withFilter(_.f2 === report.f2).length.run.toInt
                    if(len == 1) {
                        println("Update: " + report)
                        reports.update(report)
                    } else {
                        println("Insert: " + report)
                        reports.insert(report)
                    }

But I still get exception on update:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_report"
  Detail: Key ("f1", f2)=(2014-01-31 04:00:00, addon_io.aha.connect) already exists.

Answer 1

Concerning your initial question, insertOrUpdate on a table with compound keys is broken in Slick (at least with PGSql), so the error is not on your side. See bug report e.g., here: https://github.com/slick/slick/issues/966

So you have to design a workaround, however the "upsert" operation is very prone to race conditions, and is very hard to design properly as PostgreSQL does not provide native feature to perform this. See e.g., http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

Anyway, another way to perform the operation which is a bit less prone to race condition is to first update (which will do not do anything if the row does not exist), and then perform a "insert select" query, which will only insert if the row does not exist. This is the wày Slick will perform the insertOrUpdate operation on PostgreSQL with single PK. However, "insert select" cannot be done using Slick directly, you will have to fallback to direct SQL.

Answer 2

Second part where you have

val len = reports.withFilter(_.f1 === report.f1).withFilter(_.f2 === report.f2).length.run.toInt
                if(len == 1) {
                    println("Update: " + report)
                    reports.update(report)
                } else {
                    println("Insert: " + report)
                    reports.insert(report)
                }

change reports.update(report) with

reports.filter(_.id === report.id).update(report)

actually you can just make one filter call (replacing your first withFilter )