prisma/prisma

Query optimization is not compatible with PostgreSQL citext fields

murar8 opened this issue · 2 comments

murar8 commented

Bug description

As for https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance#solving-n1-in-graphql-with-findunique-and-prisma-clients-dataloader, the prisma engine will optimize multiple findUnique queries that happen in the same tick by batching the requests into a single query. This is a problem when a postgres model includes a citext field, which is case insensitive when applying the = SQL operator, but not when using the in operator.

How to reproduce

Will attach a minimal reproducible example as soon as I have some time.

Expected behavior

Ideally, findUnique will disable the optimization only for citext fields.

Prisma information

Will attach a minimal reproducible example as soon as I have some time.

Environment & setup

  • OS: Fedora 40 x64
  • Database: PostgreSQL 16
  • Node.js version: 20 LTS

Prisma Version

prisma                  : 5.3.1
@prisma/client          : 5.3.1
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.3.1-2.61e140623197a131c2a6189271ffee05a7aa9a59
Default Engines Hash    : 61e140623197a131c2a6189271ffee05a7aa9a59
Studio                  : 0.494.0
Druue commented

Hey @murar8! Could you please elaborate on what issue specifically you're running into with batching and citext?

We had another issue regarding #13534 where batched findUniques with a where on citext fields yielded incorrect results, and so we disabled batching when encountering citext in the following PR which was part of our v5.13.0 release.

I see that you mentioned that you're still on v5.3.1, if the above is the same issue that you're running into, could you please confirm whether upgrading resolves the issue?

murar8 commented

Yep, should have searched more, I can't test right now but that looks like the same problem. I will reopen the issue if I have any issues after upgrading. Thanks!

  • 👍2