Batch updates seem to have issues with uuids on 4.4.1

[TheoCadoret]

Version: 4.4.1 Module: quill-doobie Database: postgresql

Batch updates seem to have issues with uuids on 4.4.1 (works like a charm on 4.3.0) when query probing. Single updates work.

The row :

final case class PersonRow(
    id: UUID,
    name: String,
    lastName: String
)

The batch query :

def updateAll(persons: Seq[PersonRow]): ConnectionIO[Long] = run {
  liftQuery(persons).foreach { row =>
    query
      .filter(_.id == lift(row.id))
      .updateValue(row)
  }
}.map(_.sum)

The error :

 SQL query validation failed for:
[error] PREPARE p47 AS UPDATE persons AS x1 SET "id" = row.id, "name" = row.name, "last_name" = row.lastName FROM (VALUES ($1, $2, $3)) AS row(id, name, lastName) WHERE x1."id" = $4
[error] SQL error 42804: column "id" is of type uuid but expression is of type text'

Workaround

Works on 4.3.0 (without the lift around row.id , but anyways the filter does not seem to be the problem here)

Or am I missing something ?

@getquill/maintainers

[slandelle]

@deusaquilus Gentle ping. This blocks us from upgrading. We would be willing to contribute but could you please give us a hint?

[Isammoc]

I try to investigate this issue (disclaimer, I work for Gatling as well)

I work with the smaller I can manage:

In src/test/scala/io/getquill/doobie/PostgresDoobieContextSuite.scala:

  "batch update of uuid key" in {
    val ctx = io.getquill.context.jdbc.postgres.TestPostgresContextProbing
    import io.getquill.context.jdbc.postgres.TestPostgresContextProbing._
    case class QuillUuidTest(id: UUID, value: String)

    val values = List(QuillUuidTest(UUID.randomUUID(), "Foo"), QuillUuidTest(UUID.randomUUID(), "Bar"))

    val update = quote {
      liftQuery(values).foreach(a => query[QuillUuidTest].filter(_.id == a.id).updateValue(a))
    }
    ctx.run(update)
  }

with src/test/scala/io/getquill/context/jdbc/postgres/TestPostgresContextProbing.scala:

package io.getquill.context.jdbc.postgres

import io.getquill.{ Literal, PostgresJdbcContext, QueryProbing }

object TestPostgresContextProbing extends PostgresJdbcContext(Literal, "testPostgresDB") with QueryProbing

I launch the command:

POSTGRES_HOST=172.18.0.2 POSTGRES_PORT=5432 JAVA_OPTS='-Xmx8G -DdebugMacro=true -DexcludeTests=false -Dquill.macro.log.pretty=true -Dquill.macro.log=true -Dquill.trace.enabled=true -Dquill.trace.color=true -Dquill.trace.opinion=false -Dquill.trace.ast.simple=false' sbt 'quill-doobie/Test/clean' 'quill-doobie/Test/compile'

Before, in 4.3.0:

Produced SQL:

UPDATE
  QuillUuidTest AS x8
SET
  id = ?,
  value = ?
WHERE
  x8.id = ?

And the corresponding AST debug:

========== SeedRenames ==========
  Update(
    Filter(Entity("QuillUuidTest", List()), Id("x8"), BinaryOperation(x8.id, _==, ScalarTag("8e130597-7338-4fe8-b7f4-5ac1a50c49ec"))),
    List(
      Assignment(Id("v"), v.id, ScalarTag("6ed4a962-45b8-4b91-9e39-b42ebf3791d7")),
      Assignment(Id("v"), v.value, ScalarTag("29c2d4c6-78dc-4b21-905e-8eeef5d070b5"))
    )
  )
========== RepropagateQuats ==========
  Update(
    Filter(Entity("QuillUuidTest", List()), Id("x8"), BinaryOperation(x8.id, _==, ScalarTag("8e130597-7338-4fe8-b7f4-5ac1a50c49ec"))),
    List(
      Assignment(Id("v"), v.id, ScalarTag("6ed4a962-45b8-4b91-9e39-b42ebf3791d7")),
      Assignment(Id("v"), v.value, ScalarTag("29c2d4c6-78dc-4b21-905e-8eeef5d070b5"))
    )
  )
========== ApplyRenamesToProps ==========
  Update(
    Filter(Entity("QuillUuidTest", List()), Id("x8"), BinaryOperation(x8.id, _==, ScalarTag("8e130597-7338-4fe8-b7f4-5ac1a50c49ec"))),
    List(
      Assignment(Id("v"), v.id, ScalarTag("6ed4a962-45b8-4b91-9e39-b42ebf3791d7")),
      Assignment(Id("v"), v.value, ScalarTag("29c2d4c6-78dc-4b21-905e-8eeef5d070b5"))
    )
  )
========== CompleteRenames ==========
  Update(
    Filter(Entity("QuillUuidTest", List()), Id("x8"), BinaryOperation(x8.id, _==, ScalarTag("8e130597-7338-4fe8-b7f4-5ac1a50c49ec"))),
    List(
      Assignment(Id("v"), v.id, ScalarTag("6ed4a962-45b8-4b91-9e39-b42ebf3791d7")),
      Assignment(Id("v"), v.value, ScalarTag("29c2d4c6-78dc-4b21-905e-8eeef5d070b5"))
    )
  )
action sql
  Update(
    Filter(Entity("QuillUuidTest", List()), Id("x8"), BinaryOperation(x8.id, _==, ScalarTag("8e130597-7338-4fe8-b7f4-5ac1a50c49ec"))),
    List(
      Assignment(Id("x8"), x8.id, ScalarTag("6ed4a962-45b8-4b91-9e39-b42ebf3791d7")),
      Assignment(Id("x8"), x8.value, ScalarTag("29c2d4c6-78dc-4b21-905e-8eeef5d070b5"))
    )
  )

And now in 4.6.0 (master):

Produced SQL:

UPDATE
  QuillUuidTest AS x8
SET
  id = a.id1,
  value = a.value
FROM
  (
    VALUES
      (?, ?, ?)
  ) AS a(id, id1, value)
WHERE
  x8.id = a.id

And the corresponding AST debug:

========== SeedRenames ==========
  Update(
    Filter(
      Entity("QuillUuidTest", List()),
      Id("x8"),
      BinaryOperation(x8.id, _==, ScalarTag("df8aee71-e0b7-42b6-befa-65a7752a7c49", UnparsedProperty("id")))
    ),
    List(
      Assignment(Id("v"), v.id, ScalarTag("088f8a99-afbe-49f2-a180-f43b913334f2", UnparsedProperty("id"))),
      Assignment(Id("v"), v.value, ScalarTag("d5d44bbc-bf8c-4ea2-aecd-f00efb9aa455", UnparsedProperty("value")))
    )
  )
========== RepropagateQuats ==========
  Update(
    Filter(
      Entity("QuillUuidTest", List()),
      Id("x8"),
      BinaryOperation(x8.id, _==, ScalarTag("df8aee71-e0b7-42b6-befa-65a7752a7c49", UnparsedProperty("id")))
    ),
    List(
      Assignment(Id("v"), v.id, ScalarTag("088f8a99-afbe-49f2-a180-f43b913334f2", UnparsedProperty("id"))),
      Assignment(Id("v"), v.value, ScalarTag("d5d44bbc-bf8c-4ea2-aecd-f00efb9aa455", UnparsedProperty("value")))
    )
  )
========== ApplyRenamesToProps ==========
  Update(
    Filter(
      Entity("QuillUuidTest", List()),
      Id("x8"),
      BinaryOperation(x8.id, _==, ScalarTag("df8aee71-e0b7-42b6-befa-65a7752a7c49", UnparsedProperty("id")))
    ),
    List(
      Assignment(Id("v"), v.id, ScalarTag("088f8a99-afbe-49f2-a180-f43b913334f2", UnparsedProperty("id"))),
      Assignment(Id("v"), v.value, ScalarTag("d5d44bbc-bf8c-4ea2-aecd-f00efb9aa455", UnparsedProperty("value")))
    )
  )
========== CompleteRenames ==========
  Update(
    Filter(
      Entity("QuillUuidTest", List()),
      Id("x8"),
      BinaryOperation(x8.id, _==, ScalarTag("df8aee71-e0b7-42b6-befa-65a7752a7c49", UnparsedProperty("id")))
    ),
    List(
      Assignment(Id("v"), v.id, ScalarTag("088f8a99-afbe-49f2-a180-f43b913334f2", UnparsedProperty("id"))),
      Assignment(Id("v"), v.value, ScalarTag("d5d44bbc-bf8c-4ea2-aecd-f00efb9aa455", UnparsedProperty("value")))
    )

The issue

on main (4.6.0), the query probing failed with:

[error] [redacted]/zio-quill/quill-doobie/src/test/scala/io/getquill/doobie/PostgresDoobieContextSuite.scala:122:12: Query probing failed. Reason: 'org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = text
[error]   Indice : No operator matches the given name and argument type(s). You might need to add explicit type casts.
[error]   Position : 135'
[error]     ctx.run(update)
[error]            ^

So I tried to understand. In a psql terminal:

# psql -U postgres -d quill_test
psql (9.6.24)
Type "help" for help.

quill_test=# PREPARE p1 AS
quill_test-#     UPDATE
quill_test-#       QuillUuidTest AS x8
quill_test-#     SET
quill_test-#       id = $1,
quill_test-#       value = $2
quill_test-#     WHERE
quill_test-#       x8.id = $3
quill_test-# ;
PREPARE
quill_test=# PREPARE p2 AS
quill_test-#     UPDATE
quill_test-#         QuillUuidTest AS x8
quill_test-#     SET
quill_test-#         id = a.id1,
quill_test-#         value = a.value
quill_test-#     FROM
quill_test-#         (
quill_test(#             VALUES
quill_test(#                 ($1, $2, $3)
quill_test(#         ) AS a(id, id1, value)
quill_test-#     WHERE
quill_test-#             x8.id = a.id
quill_test-# ;
ERROR:  operator does not exist: uuid = text
LINE 13:             x8.id = a.id
                           ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
quill_test=# PREPARE p3 (uuid, uuid, text) AS
quill_test-#     UPDATE
quill_test-#         QuillUuidTest AS x8
quill_test-#     SET
quill_test-#         id = a.id1,
quill_test-#         value = a.value
quill_test-#     FROM
quill_test-#         (
quill_test(#             VALUES
quill_test(#                 ($1, $2, $3)
quill_test(#         ) AS a(id, id1, value)
quill_test-#     WHERE
quill_test-#         x8.id = a.id
quill_test-# ;
PREPARE
quill_test=# 

Weirdly, postgres does not complain about types in the first version (used by 4.3.0), but does in the second one (used by 4.6.0). With what I understand, the fix would be to query probe with the third version (ie, with types declaration). The real issue is that we already lost the typing of parameters when calling idiom.prepareForProbing.

At this point, I don't even know if this is possible to retain such typings or the effort needed to keep types until that statement.

@getquill/maintainers Am I on the right tracks?

Our options:

• find how to keep types of parameters until that ProbeStatement
• or quit query probing in our project (should still work when provided real values?)

[tpetillot]

Few other notes:

Casting type on value table does work aswell:

quill_test=# PREPARE p1 AS
quill_test-#     UPDATE
quill_test-#         QuillUuidTest AS x8
quill_test-#     SET
quill_test-#         id = a.id1,
quill_test-#         value = a.value
quill_test-#     FROM
quill_test-#         (
quill_test(#             VALUES
quill_test(#                 ($1::uuid, $2::uuid, $3::text)
quill_test(#         ) AS a(id, id1, value)
quill_test-#     WHERE
quill_test-#         x8.id = a.id
quill_test-# ;
PREPARE

Also, any type other than text would lead to a failure (nothing else is inferred from the value table by default).

[devsprint]

Same issue is happening with quill-jdbc-zio. It looks like BatchInsert code generation is broken even if pk is VARCHAR not only when is of type UUID

[guizmaii]

@getquill/maintainers this issue seems to be blocking quite a few people. Can we have a look, please?

[apavlychev]

Same issue with Option json and boolean fields. Dont work batch update via method update and updateValue

[apavlychev]

how workaround may use bulk-insert via insertValue(...).onUpdateConflict(...).

P.S. its interesting that insert-query have same raw with a incorrect type's casting but jdbc driver do casting itself

[tpetillot]

Any update for this issue?

[kolemannix]

Kindly bumping this; we're completely frozen in time by this issue. Even an investigation into a workaround by someone knowledgeable of Quill's internals would be super valuable. Thanks.

[deusaquilus]

Looking into this now. The problem here is with the postgres multi-batch functionality that I introduced. The simplest way to fix it should be to disable multi-value batches.