Duplicate URLs in the Same Feed Caused a Complete Failure of ON CONFLICT DO UPDATE with Error 21000
機械翻訳 / Machine-translated
機械翻訳 / Machine-translated
A job that upserted multiple articles with a single ON CONFLICT DO UPDATE statement was silently skipping an entire feed due to error 21000 — "ON CONFLICT DO UPDATE command cannot affect row a second time" — triggered by duplicate URLs within the same feed, while a fail-soft catch was swallowing the error without any noise.
When you batch-upsert with a single INSERT statement, having just two rows with the same conflict target is enough to fail the entire statement. A job that fetched news articles and saved them to the database was issuing one INSERT ... ON CONFLICT (source, url) DO UPDATE per feed. Whenever a feed contained two articles with the same URL, Postgres raised error 21000 — ON CONFLICT DO UPDATE command cannot affect row a second time — causing that entire feed to stop updating silently.
A detailed walkthrough, how to reproduce the issue, and frequently asked questions are covered in the original article.