MagicStack/asyncpg

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

Opened this issue · 6 comments

doiodl commented
  • asyncpg version: 0.29.0
  • PostgreSQL version: 14.9
  • Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
    the issue with a local PostgreSQL install?    : no
  • Python version: 3.11.6
  • Platform: macos, arm, m1 pro
  • Do you use pgbouncer?: no
  • Did you install asyncpg with pip?: yes
  • If you built asyncpg locally, which version of Cython did you use?: no
  • Can the issue be reproduced under both asyncio and
    uvloop?    : yes

Hello everyone. So, I've written a fairly simple code that updates a table based on a composite index. I'm using sqlalchemy 2.0.28. The query is generated correctly, but exactly on the 6th query to the database, asyncpg supposedly throws an error:

<class 'asyncpg.exceptions.InvalidColumnReferenceError'>: there is no unique or exclusion constraint matching the ON CONFLICT specification

Although, as I mentioned - the first 5 queries work fine. Reloading the application (fastapi) or rather creating a new connection, everything starts working again for the first 5 queries. I looked for the reason in sqlalchemy since there pool_size = 5. Changing this value still gives me the same error.

elprans commented

This isn't asyncpg throwing an error, this is PostgreSQL complaining that your SQL is incorrect. Check your ON CONFLICT clause carefully and make sure it matches your index and also that the index is unique.

krigar1184 commented

@doiodl have you found out the error cause? I'm experiencing the same error with asyncpg=0.29.0, postgres=15.6 and fastapi=0.79.0. Query runs normally twice and fails on the third attempt with input being the same. Running the same query multiple times in a database client doesn't result in error.

quark-tekliner commented

Hi guys. I faced similar issue. In my case the issue occurs in query like INSERT...ON CONFLICT (foo, bar) WHERE foo = $1 DO UPDATE.... For me asyncpg.connect(..., statement_cache_size=0) did the trick.

roblperry commented

PG gets into a state where I get the error described above for the query:

INSERT INTO "identity" ("signature_id", "environment_id", "organization_uuid", "is_active") 
VALUES ($1, $2, $3, $4) 
ON CONFLICT ("signature_id") WHERE "identity"."is_active" = $5 
DO UPDATE SET "environment_id" = "excluded"."environment_id", "organization_uuid" = "excluded"."organization_uuid" WHERE ("identity"."environment_id" IS DISTINCT FROM "excluded"."environment_id") OR ("identity"."organization_uuid" IS DISTINCT FROM "excluded"."organization_uuid")

for a certain set of values. I can retry those values many times and continue to get the same error. However, other values will work, and once they have, the original problematic set of values will also work. This has been true for both PG 15.3 and 15.8. I've been dealing with this for weeks. I fear I'm going to have to abandon use of ON CONFLICT were the conflict involves partial indices.

Thesohan commented

@roblperry @quark-tekliner @doiodl @elprans Have you got any solution/workaround for this?
I'm also facing a similar issue.

roblperry commented

@Thesohan , thank you for reminding me to add a comment. Leaving out a lot of superfluous details about my adventures, the problem is the "identity"."is_active" = $5. It's fine as long as the query is being immediately executed and, of course, $5 matches the value required by the partial index.

My problem was that that it was eventually being turned into a prepared query and as a prepared query, the $5 is unknown, thus the partial index cannot be used. So, is just had to smack my ORM around a bit, so that it produces:

INSERT INTO "identity" ("signature_id", "environment_id", "organization_uuid", "is_active") 
VALUES ($1, $2, $3, $4) 
ON CONFLICT ("signature_id") WHERE "identity"."is_active" = true 
DO UPDATE SET "environment_id" = "excluded"."environment_id", "organization_uuid" = "excluded"."organization_uuid" WHERE ("identity"."environment_id" IS DISTINCT FROM "excluded"."environment_id") OR ("identity"."organization_uuid" IS DISTINCT FROM "excluded"."organization_uuid")
  • 👍1