Duplicate URLs in a Feed Break ON CONFLICT DO UPDATE

Conclusion

Upserting in one batched INSERT means two rows with the same conflict target fail the entire statement. A job that fetches news articles and saves them to the database issued one INSERT ... ON CONFLICT (source, url) DO UPDATE per feed. With two articles sharing a URL inside the same feed, it failed with Postgres 21000, ON CONFLICT DO UPDATE command cannot affect row a second time, and that feed stopped updating in silence.

Symptom

The job periodically fetches several external RSS feeds and upserts into a news_items table to refresh the signage headline cache. Processing one feed — fetch, parse, save — is wrapped in a try/catch so a failing feed is skipped and the others continue: fail-soft.

When one feed contains two articles with the same URL, the save call fails with Postgres 21000. The error is absorbed by that try/catch and nothing propagates outward. The skipped feed’s existing cache stays as last-known-good, so on screen there is nothing beyond “this one news source has stopped updating” — a symptom that is easy to miss.

Cause

Saving is handled by saveNewsItems, which issues one INSERT statement for all the articles it receives in a call.

// packages/db/src/queries/news-items.ts
const rows = await tx
  .insert(newsItems)
  .values(
    items.map((it) => ({
      source: it.source,
      sourceLabel: it.sourceLabel,
      title: it.title,
      url: it.url,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [newsItems.source, newsItems.url],
    set: {
      sourceLabel: sql`excluded.source_label`,
      title: sql`excluded.title`,
      // ...
      updatedAt: sql`now()`,
    },
  })
  .returning({ id: newsItems.id });

target: [newsItems.source, newsItems.url] corresponds to the unique index ux_news_items_source_url on the schema side, over source and url.

// packages/db/src/schema/news-items.ts
(t) => ({
  // one row per feed x article URL (re-fetch upserts via ON CONFLICT (source, url)).
  uxSourceUrl: uniqueIndex("ux_news_items_source_url").on(t.source, t.url),
}),

The job passes saveItems an array of articles with source fixed for one feed. Since the source is constant, two articles sharing a URL within a feed put two rows with an identical (source, url) into that single INSERT. Postgres’s ON CONFLICT DO UPDATE does not permit updating the same row twice within one statement and rejects it as 21000, cannot affect row a second time. Rows in an INSERT are processed without seeing the other rows of the same statement, so unless duplicates are removed beforehand, the whole statement fails together.

The fix

A first-occurrence-wins deduplication keyed on URL was inserted just before calling saveItems.

// apps/jobs/src/news/run.ts
// Remove duplicate URLs within one feed (first wins). saveItems issues a single
// INSERT ... ON CONFLICT (source, url) DO UPDATE, so two rows with the same url in one feed
// (source constant) make Postgres fail with 21000 "ON CONFLICT DO UPDATE ... cannot affect
// row a second time", and the whole feed's save is silently lost (the source stops updating).
const seenUrl = new Set<string>();
const items = mapped.filter((it) => {
  if (seenUrl.has(it.url)) return false;
  seenUrl.add(it.url);
  return true;
});
const rows = await deps.saveItems(items);

saveNewsItems itself — the INSERT statement and the schema — is unchanged. Removing duplicates on the input side, before anything is passed to a single statement, prevents the situation where ON CONFLICT DO UPDATE would try to update the same row twice from arising at all.

A regression test pinning the behaviour with duplicate URLs in one feed was added with the fix.

// apps/jobs/src/news/__tests__/run.test.ts
it("removes duplicate URLs within one feed before passing to saveItems (first wins)", async () => {
  const dup: ParsedNewsItem = {
    title: "記事A（重複・後勝ち破棄）",
    url: "https://a/1", // same url as ITEM_A
    publishedAt: new Date("2026-06-02T00:00:00Z"),
    summary: null,
  };
  const summary = await runNewsFetch({
    listFeeds: () => [FEED_MEXT],
    fetchFeed: async () => [ITEM_A, ITEM_B, dup],
    saveItems,
  });

  const passed = saveItems.mock.calls[0]?.[0] as ReadonlyArray<{ url: string; title: string }>;
  expect(passed).toHaveLength(2); // one duplicate removed
  expect(passed.map((i) => i.url)).toEqual(["https://a/1", "https://a/2"]);
  // First wins: ITEM_A, which appeared first, survives.
  expect(passed[0]?.title).toBe("記事A");
});

An assumption worth naming

A batch upsert cannot see its own rows. Each row inside one INSERT is processed without reference to the others, so a duplicate conflict target is only detected by Postgres refusing the whole statement. Wherever several rows are passed to one ON CONFLICT DO UPDATE, deduplicating on the input side is a precondition, not an optimisation.

A fail-soft catch converts this into silence. Skipping a failed feed and continuing is the right design for a job fetching many external sources — but it also means the only observable symptom is one source quietly not updating. The exception never reaches anyone.

This was found in a bug-hunting sweep, not in production. Nobody reported it. That is consistent with the shape of the failure: the cache stays as last-known-good, so the screen keeps showing plausible headlines from before the break.