Duplicate violates unique constraint "session_pkey" errors in PostgreSQL logs

[ddxv]

Describe the Bug

I also get these errors, they were present in V2 and now V3 so I don't think there was any specific change there. This issue was also reported, and fixed, and closed in #3159 but I have been seeing this for quite some time in V2 and now still in V3. Feel free to close/merge this with #3159 if we'd like to keep the conversation there.

The errors show up at a many per second in my postgresql.log and I guess are from various AI crawlers which crawl my site multiple times per second. Rather than erroring out perhaps Umami should use ON CONFLICT DO NOTHING?

2025-11-09 02:31:56.209 UTC [817254] postgres@umamidb ERROR:  duplicate key value violates unique constraint "session_pkey"
2025-11-09 02:31:56.209 UTC [817254] postgres@umamidb DETAIL:  Key (session_id)=(beb09cd8-3b19-5056-b9c5-f20608ea8b1d) already exists.
2025-11-09 02:31:56.209 UTC [817254] postgres@umamidb STATEMENT:  INSERT INTO "public"."session" ("session_id","website_id","browser","os","device","screen","language","country","city","created_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "public"."session"."session_id", "public"."session"."website_id", "public"."session"."browser", "public"."session"."os", "public"."session"."device", "public"."session"."screen", "public"."session"."language", "public"."session"."country", "public"."session"."region", "public"."session"."city", "public"."session"."distinct_id", "public"."session"."created_at"
2025-11-09 02:31:56.466 UTC [817255] postgres@umamidb ERROR:  duplicate key value violates unique constraint "session_pkey"
2025-11-09 02:31:56.466 UTC [817255] postgres@umamidb DETAIL:  Key (session_id)=(2ceddd63-3ab7-58fa-9b29-aa1de18c9581) already exists.
2025-11-09 02:31:56.466 UTC [817255] postgres@umamidb STATEMENT:  INSERT INTO "public"."session" ("session_id","website_id","browser","os","device","screen","language","country","city","created_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "public"."session"."session_id", "public"."session"."website_id", "public"."session"."browser", "public"."session"."os", "public"."session"."device", "public"."session"."screen", "public"."session"."language", "public"."session"."country", "public"."session"."region", "public"."session"."city", "public"."session"."distinct_id", "public"."session"."created_at"
2025-11-09 02:31:56.674 UTC [817256] postgres@umamidb ERROR:  duplicate key value violates unique constraint "session_pkey"
2025-11-09 02:31:56.674 UTC [817256] postgres@umamidb DETAIL:  Key (session_id)=(1d400a23-53dd-5d45-a701-0bfdabe2e41c) already exists.
2025-11-09 02:31:56.674 UTC [817256] postgres@umamidb STATEMENT:  INSERT INTO "public"."session" ("session_id","website_id","browser","os","device","screen","language","country","city","created_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "public"."session"."session_id", "public"."session"."website_id", "public"."session"."browser", "public"."session"."os", "public"."session"."device", "public"."session"."screen", "public"."session"."language", "public"."session"."country", "public"."session"."region", "public"."session"."city", "public"."session"."distinct_id", "public"."session"."created_at"
2025-11-09 02:31:57.496 UTC [817257] postgres@umamidb ERROR:  duplicate key value violates unique constraint "session_pkey"
2025-11-09 02:31:57.496 UTC [817257] postgres@umamidb DETAIL:  Key (session_id)=(5c703b6b-d0b9-59c2-b089-b2e4dfc7b82e) already exists.
2025-11-09 02:31:57.496 UTC [817257] postgres@umamidb STATEMENT:  INSERT INTO "public"."session" ("session_id","website_id","browser","os","device","screen","language","country","city","created_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "public"."session"."session_id", "public"."session"."website_id", "public"."session"."browser", "public"."session"."os", "public"."session"."device", "public"."session"."screen", "public"."session"."language", "public"."session"."country", "public"."session"."region", "public"."session"."city", "public"."session"."distinct_id", "public"."session"."created_at"

Database

PostgreSQL

Which Umami version are you using? (if relevant)

3.0

[franciscao633]

I tried looking at this history to see if anything was done. I think ON CONFLICT DO NOTHING makes the most sense. Need to see if this option is available in prisma or we can potentially convert it to a raw query.

[ddxv]

@franciscao633 Yes, that does work, but FYI behaviors could change.

For example, changing an 'ON CONFLICT DO UPDATE SET to ON CONFLICT DO NOTHING` will not automatically return rows (as sometimes I use CONFLICT as a lazy way to 'upsert').

Just wanted to throw that out there in case it was a gotcha, but might not be relevant in this situation.