upsert on compound primary key should do ON CONFLICT DO ... in postgresql

[futpib]

Bug description

When using a compound primary key like this:

@@id([referrerEventId, refereeEventId])

And doing an upsert like this:

await this._prisma.eventReferenceRelation.upsert({
	where: {
		referrerEventId_refereeEventId: {
			referrerEventId,
			refereeEventId,
		},
	},
	update: {
		recommendedRelayUrl,
	},
	create: {
		referrerEventId,
		refereeEventId,
		recommendedRelayUrl,
	},
});

Postgres receices SQL update query without an on conflict:

INSERT INTO "public"."EventReferenceRelation" ("referrerEventId","refereeEventId")
	VALUES ($1,$2)
	RETURNING "public"."EventReferenceRelation"."referrerEventId", "public"."EventReferenceRelation"."refereeEventId"

Which results in the following exception being thrown:

  Invalid `this._prisma.eventReferenceRelation.upsert()` invocation in
  /usr/src/app/packages/nostter-backend/dist/src/event/event.service.js:239:55
  
    236 }
    237 async addReferenceRelations(referenceRelations) {
    238     for (const referenceRelation of referenceRelations) {
  → 239         await this._prisma.eventReferenceRelation.upsert(
  Unique constraint failed on the fields: (`referrerEventId`,`refereeEventId`)

This defies the purpose of upsert.

Related: #9972

How to reproduce

Description is basically a reproduction.

Expected behavior

Prisma generates a proper upsert SQL with an ON CONFLICT clause handing the conflict.

Prisma information

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum UnitType {
  UnitValue
}

model Relay {
  url String @id

  EventReferenceRelation EventReferenceRelation[]
}

model Event {
  id        String   @id
  sig       String
  pubkey    String
  kind      BigInt
  tags      Json
  content   String
  createdAt DateTime

  referenceRelations_referrer EventReferenceRelation[] @relation("EventReferenceRelation_referrer")
  referenceRelations_referee  EventReferenceRelation[] @relation("EventReferenceRelation_referee")

  deletionRelations_deleter EventDeletionRelation[] @relation("EventDeletionRelation_deleter")
  deletionRealtions_deletee EventDeletionRelation[] @relation("EventDeletionRelation_deletee")

  firstDeleterEventId String?
  firstDeleterEvent   Event?  @relation("EventFirstDeletionRelation", fields: [firstDeleterEventId], references: [id])
  firstDeleteeEvents  Event[] @relation("EventFirstDeletionRelation")

  reactionRelations_reacter EventReactionRelation[] @relation("EventReactionRelation_reacter")
  reactionRelations_reactee EventReactionRelation[] @relation("EventReactionRelation_reactee")

  height BigInt @unique @default(autoincrement())

  eventReactionCountState EventReactionCountState?
}

model EventReferenceRelation {
  referrerEventId String
  refereeEventId  String

  referrerEvent Event @relation("EventReferenceRelation_referrer", fields: [referrerEventId], references: [id])
  refereeEvent  Event @relation("EventReferenceRelation_referee", fields: [refereeEventId], references: [id])

  recommendedRelayUrl String?
  recommendedRelay    Relay?  @relation(fields: [recommendedRelayUrl], references: [url])

  @@id([referrerEventId, refereeEventId])
}

model EventDeletionRelation {
  deleterEventId String
  deleteeEventId String

  deleterEvent Event @relation("EventDeletionRelation_deleter", fields: [deleterEventId], references: [id])
  deleteeEvent Event @relation("EventDeletionRelation_deletee", fields: [deleteeEventId], references: [id])

  @@id([deleterEventId, deleteeEventId])
}

model EventReactionRelation {
  reacterEventId String
  reacteeEventId String

  reacterEvent Event @relation("EventReactionRelation_reacter", fields: [reacterEventId], references: [id])
  reacteeEvent Event @relation("EventReactionRelation_reactee", fields: [reacteeEventId], references: [id])

  @@id([reacterEventId, reacteeEventId])
}

model EventResolveEventPointersState {
  id UnitType @id

  height BigInt
}

model EventReferenceRelationState {
  id UnitType @id

  height BigInt
}

model EventDeletionRelationState {
  id UnitType @id

  height BigInt
}

model EventReactionRelationState {
  id UnitType @id

  height BigInt
}

model EventReactionCountState {
  id String @id @default(uuid())

  eventId String @unique
  event   Event  @relation(fields: [eventId], references: [id])

  height BigInt

  reactionCounts Json
}

			await this._prisma.eventReferenceRelation.upsert({
				where: {
					referrerEventId_refereeEventId: {
						referrerEventId: referenceRelation.referrerEventId,
						refereeEventId: referenceRelation.refereeEventId,
					},
				},
				update: {
					recommendedRelayUrl: referenceRelation.recommendedRelayUrl,
				},
				create: referenceRelation,
			});

Environment & setup

• OS: Alpine Linux (in Docker) on Arch Linux (but it's irrelevant)
• Database: PostgreSQL
• Node.js version: v19.8.1 (but it's irrelevant)

Prisma Version

Environment variables loaded from .env
prisma                  : 4.13.0
@prisma/client          : 4.13.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a (at ../../node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a (at ../../node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Format Wasm             : @prisma/prisma-fmt-wasm 4.13.0-50.1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a
Default Engines Hash    : 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a
Studio                  : 0.484.0

[janpio]

Hey @futpib, thanks for opening an issue.

When did Prisma try to execute the query you shared with us - on the insert (data does not exist) or the update (data does already exist) case? I am confused why you have an INSERT with only the two compound primary key values, but no additional column being written.

[janpio]

I quickly tried to reproduce this.

I manually (via npx prisma studio created two Events with id 1 and 2, and a Relay with url=recommendedRelayUrl).
Afterwards this code here works:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient({ log: ['query']})

async function main() {
  const referrerEventId = '1'
  const refereeEventId = '2'
  const recommendedRelayUrl = 'recommendedRelayUrl'

  await prisma.eventReferenceRelation.upsert({
    where: {
      referrerEventId_refereeEventId: {
        referrerEventId,
        refereeEventId,
      },
    },
    update: {
      recommendedRelayUrl,
    },
    create: {
      referrerEventId,
      refereeEventId,
      recommendedRelayUrl,
    },
  });
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

It executes this SQL:

> node .\script.js
prisma:query INSERT INTO "public"."EventReferenceRelation" ("referrerEventId","refereeEventId","recommendedRelayUrl") VALUES ($1,$2,$3) ON CONFLICT ("referrerEventId","refereeEventId") DO UPDATE SET "recommendedRelayUrl" = $4 WHERE (("public"."EventReferenceRelation"."referrerEventId" = $5 AND "public"."EventReferenceRelation"."refereeEventId" = $6) AND 1=1) RETURNING "public"."EventReferenceRelation"."referrerEventId", "public"."EventReferenceRelation"."refereeEventId", "public"."EventReferenceRelation"."recommendedRelayUrl"

This works in both the insert and the update case.

What am I missing?

[calvinl]

@janpio i just ran across this same issue. I actually tried to reduce the reproducible test case by using a single unique column, but it still doesn't generate the native query.

Is there something I'm missing? Does this require a specific Postgres version to use

I'm using Prisma client 4.15.0. Postgres version is 14.7. Pretty baffled by this.

[calvinl]

I may have just figured this out, at least for my specific case. It appears that when using upsert as a "find or create", where the update field is left as an empty object, Prisma does not hand off the upsert to the native database. I would expect in the case where update is empty, we would issue a DO NOTHING instead?

@janpio thoughts? i can open a new ticket for this if this is valid.

[janpio]

That might indeed be a case where our "native upsert" logic is not triggered or similar - so best open a new issue indeed @calvinl. It also sounds like your case is different enough from what @futpib had described above, to really justify a different issue. Do you agree?

[ahammouda]

I may have just figured this out, at least for my specific case. It appears that when using upsert as a "find or create", where the update field is left as an empty object, Prisma does not hand off the upsert to the native database. I would expect in the case where update is empty, we would issue a DO NOTHING instead?

@janpio thoughts? i can open a new ticket for this if this is valid.

I've come across this issue as well @calvinl - would love to see this get worked on.

[calvinl]

@janpio I'll open a ticket now, thanks for the response!

@ahammouda -- I was able to get around this by just putting a field in the update property to update some arbitrary field with the same value. It's not ideal though, but it does work.

[ruheni]

👋 @futpib,

Have you had a chance to read the other comments in this issue? Could you confirm that the suggestions resolve your issue? We would appreciate your input to help us resolve this issue if it's a bug.

[jkomyno]

Hey @futpib, as @ruheni asked, could you please confirm that the comments above would solve your problem, and hence that we can consider this issue "closed as solved"? Thanks!

[DRoet]

Hi @calvinl , which ticket did you create for this? updating an arbitrary field is a solid workaround but I had to go through a lot of reading before I stumbled upon this issue to solve it on my end.

[calvinl]

@DRoet oops, I never opened one. I ended up switching off of prisma to kysely shortly after this. Feel free to open one though!

[AllanOricil]

I'm also facing this problem with prisma v5.6

model Instance {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  aws_id    String?
  aws_instance_state AWS_INSTANCE_STATE
  subdomain String  @unique
  state     INSTANCE_STATE
  tier_id    String  @db.ObjectId
  tier      InstanceSubscriptionTier @relation(fields: [tier_id], references: [id])
  account_id String  @db.ObjectId
  account   Account @relation(fields: [account_id], references: [id])
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  @@map("instances")
  @@index([account_id, subdomain], name: "account_id_subdomain_unique")
}

2023-12-12T02:32:37.056Z	f3e3f645-0f34-5b41-889c-abb4c74cab10	ERROR	Invoke Error 	{
    "errorType": "PrismaClientValidationError",
    "errorMessage": "\nInvalid `prisma.instance.upsert()` invocation:\n\n{\n  where: {\n    account_id_subdomain_unique: {\n    ~~~~~~~~~~~~~~~~~~~~~~~~~~~\n      account_id: \"6570eb74a0a5f0d856f2db82\",\n      subdomain: \"test\"\n    },\n?   id?: String,\n?   subdomain?: String,\n?   AND?: InstanceWhereInput | InstanceWhereInput[],\n?   OR?: InstanceWhereInput[],\n?   NOT?: InstanceWhereInput | InstanceWhereInput[],\n?   aws_id?: StringNullableFilter | String | Null,\n?   aws_instance_state?: EnumAWS_INSTANCE_STATEFilter | AWS_INSTANCE_STATE,\n?   state?: EnumINSTANCE_STATEFilter | INSTANCE_STATE,\n?   tier_id?: StringFilter | String,\n?   account_id?: StringFilter | String,\n?   created_at?: DateTimeFilter | DateTime,\n?   updated_at?: DateTimeFilter | DateTime,\n?   tier?: InstanceSubscriptionTierRelationFilter | InstanceSubscriptionTierWhereInput,\n?   account?: AccountRelationFilter | AccountWhereInput\n  },\n  update: {\n    account_id: \"6570eb74a0a5f0d856f2db82\",\n    aws_id: undefined,\n    aws_instance_state: \"PENDING\",\n    subdomain: \"test\",\n    state: \"UNAVAILABLE\",\n    tier_id: \"6570ed90d09e2638e19eeeff\"\n  },\n  create: {\n    account_id: \"6570eb74a0a5f0d856f2db82\",\n    aws_id: undefined,\n    aws_instance_state: \"PENDING\",\n    subdomain: \"test\",\n    state: \"UNAVAILABLE\",\n    tier_id: \"6570ed90d09e2638e19eeeff\"\n  }\n}\n\nUnknown argument `account_id_subdomain_unique`. Available options are marked with ?.",
    "name": "PrismaClientValidationError",
    "clientVersion": "5.6.0",
    "stack": [
        "PrismaClientValidationError: ",
        "Invalid `prisma.instance.upsert()` invocation:",
        "",
        "{",
        "  where: {",
        "    account_id_subdomain_unique: {",
        "    ~~~~~~~~~~~~~~~~~~~~~~~~~~~",
        "      account_id: \"6570eb74a0a5f0d856f2db82\",",
        "      subdomain: \"test\"",
        "    },",
        "?   id?: String,",
        "?   subdomain?: String,",
        "?   AND?: InstanceWhereInput | InstanceWhereInput[],",
        "?   OR?: InstanceWhereInput[],",
        "?   NOT?: InstanceWhereInput | InstanceWhereInput[],",
        "?   aws_id?: StringNullableFilter | String | Null,",
        "?   aws_instance_state?: EnumAWS_INSTANCE_STATEFilter | AWS_INSTANCE_STATE,",
        "?   state?: EnumINSTANCE_STATEFilter | INSTANCE_STATE,",
        "?   tier_id?: StringFilter | String,",
        "?   account_id?: StringFilter | String,",
        "?   created_at?: DateTimeFilter | DateTime,",
        "?   updated_at?: DateTimeFilter | DateTime,",
        "?   tier?: InstanceSubscriptionTierRelationFilter | InstanceSubscriptionTierWhereInput,",
        "?   account?: AccountRelationFilter | AccountWhereInput",
        "  },",
        "  update: {",
        "    account_id: \"6570eb74a0a5f0d856f2db82\",",
        "    aws_id: undefined,",
        "    aws_instance_state: \"PENDING\",",
        "    subdomain: \"test\",",
        "    state: \"UNAVAILABLE\",",
        "    tier_id: \"6570ed90d09e2638e19eeeff\"",
        "  },",
        "  create: {",
        "    account_id: \"6570eb74a0a5f0d856f2db82\",",
        "    aws_id: undefined,",
        "    aws_instance_state: \"PENDING\",",
        "    subdomain: \"test\",",
        "    state: \"UNAVAILABLE\",",
        "    tier_id: \"6570ed90d09e2638e19eeeff\"",
        "  }",
        "}",
        "",
        "Unknown argument `account_id_subdomain_unique`. Available options are marked with ?.",
        "    at Zn (/var/task/node_modules/@prisma/client/runtime/library.js:117:5888)",
        "    at ni.handleRequestError (/var/task/node_modules/@prisma/client/runtime/library.js:124:6516)",
        "    at ni.handleAndLogRequestError (/var/task/node_modules/@prisma/client/runtime/library.js:124:6206)",
        "    at ni.request (/var/task/node_modules/@prisma/client/runtime/library.js:124:5926)",
        "    at async l (/var/task/node_modules/@prisma/client/runtime/library.js:129:10023)",
        "    at async InstanceRepository.upsertByAccountIdAndSubdomain (/var/task/index.js:181:5)",
        "    at async InstancesController.createOne (/var/task/index.js:486:23)",
        "    at async Runtime.main [as handler] (/var/task/index.js:712:23)"
    ]
}

[janpio]

Can you turn this into a standalone issue please @AllanOricil? As you can see in the messages above, you might not be the first one - so we should look into this. Thanks!