upsert arbitrates ON CONFLICT on a partial unique index instead of the where-selected unique, failing with 42P10 (partialIndexes)

[wongk]

Bug description

With previewFeatures = ["partialIndexes"], a native upsert whose where names a total (non-partial) compound unique can generate ON CONFLICT on the columns of a partial unique index declared on the same model, instead of on the unique actually referenced by where.

Postgres rejects that statement unconditionally with SQLSTATE 42P10 — a bare-column ON CONFLICT (col) arbiter can never be satisfied by a partial index (inference requires the index predicate, which the generated SQL does not include). So the upsert fails on every call, at SQL-generation time, regardless of data.

Declaring the partial unique in the schema is the trigger: the same model with the partial index left undeclared (hand-carried in migration SQL, invisible to the client) generates the correct arbiter. This bit us in production — declaring 25 existing partial indexes in the schema (a comment-level change from the app's perspective; no migrations, no app-code changes) silently flipped one ingest path's upsert arbiter and failed 100% of its calls for ~43 hours.

Generated SQL with the partial unique declared (arbiter is the partial's column — fails 42P10):

INSERT INTO "public"."generations" ("id","session_id","external_id","revision","active_at")
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT ("session_id") DO UPDATE SET "active_at" = $6
WHERE (("public"."generations"."session_id" = $7 AND "public"."generations"."external_id" = $8 AND "public"."generations"."revision" = $9) AND 1=1)
RETURNING ...

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Generated SQL with the partial unique removed from the schema (identical client call — correct arbiter, succeeds):

INSERT INTO "public"."generations" ("id","session_id","external_id","revision","active_at")
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT ("session_id","external_id","revision") DO UPDATE SET "active_at" = $6
WHERE (...)
RETURNING ...

Related (not duplicates, same preview-feature family): #29282 (WhereUniqueInput generated for partial uniques), #29263 (partialIndexes migration churn).

How to reproduce

1. Schema (prisma/schema.prisma):

generator client {
  provider        = "prisma-client"
  output          = "../generated"
  previewFeatures = ["partialIndexes"]
}

datasource db {
  provider = "postgresql"
}

model Generation {
  id         String    @id
  // Partial unique: at most one "active" row per session.
  sessionId  String    @unique(map: "one_active_per_session", where: raw("(active_at IS NOT NULL)")) @map("session_id")
  externalId String    @map("external_id")
  revision   Int
  activeAt   DateTime? @map("active_at")

  // Total (non-partial) identity unique — the one upsert targets via `where`.
  @@unique([sessionId, externalId, revision], map: "generation_identity")
  @@map("generations")
}

2. prisma db push --accept-data-loss && prisma generate (indexes land exactly as declared):

Indexes:
    "generations_pkey" PRIMARY KEY, btree (id)
    "generation_identity" UNIQUE, btree (session_id, external_id, revision)
    "one_active_per_session" UNIQUE, btree (session_id) WHERE active_at IS NOT NULL

3. Run:

import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/client.ts";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

await prisma.generation.upsert({
  where: {
    sessionId_externalId_revision: { sessionId: "s1", externalId: "g1", revision: 1 },
  },
  create: { id: "row-1", sessionId: "s1", externalId: "g1", revision: 1, activeAt: new Date() },
  update: { activeAt: new Date() },
});

Observed: ON CONFLICT ("session_id") → 42P10 on every call (empty table included).

4. Delete the @unique(..., where: raw(...)) attribute from sessionId (leaving the identical partial index in the database), push + regenerate, rerun the identical call: ON CONFLICT ("session_id","external_id","revision"), succeeds.

Expected behavior

upsert should arbitrate on the unique constraint the where clause names (generation_identity), never on a partial unique — or, if a partial unique is ever the intended arbiter, the generated ON CONFLICT must carry the index predicate (ON CONFLICT ("session_id") WHERE active_at IS NOT NULL) so Postgres can infer it.

Prisma information

• prisma / @prisma/client: 7.8.0 (engines 7.8.0-6.3c6e192761c0362d496ed980de936e2f3cebcd3a)
• Driver adapter: @prisma/adapter-pg 7.8.0
• previewFeatures = ["partialIndexes"]

Environment & setup

• OS: macOS 26.6.2 (arm64)
• Database: PostgreSQL 16.14 (Debian, aarch64, Docker)
• Node.js: v26.7.0

Prisma engine query

See generated SQL above (captured via Postgres log_statement = 'all'; the client-side query event shows the same statement).