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

[doiodl]

• 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]

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]

@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.