Thousands of duplicate key errors on metadata_to_metadata: ON CONFLICT targets the wrong column

[TheQuietBofh]

Description

Every time metadata is refreshed, Postgres logs thousands of duplicate key errors for the metadata_to_metadata table.

The insert uses ON CONFLICT ("id") DO NOTHING, but the unique constraint that is actually violated is the composite index on (from_metadata_id, relation, to_metadata_id).

Because id is always new, the conflict clause never matches, and every already-existing suggestion raises an error instead of being silently skipped.

Expected behaviour

Re-inserting an existing relation should be a no-op.

Actual behaviour

Postgres rejects each row individually and logs one error per row.

Logs

ERROR:  duplicate key value violates unique constraint
        "metadata_to_metadata_from_metadata_id_relation_to_metadata__idx"
DETAIL:  Key (from_metadata_id, relation, to_metadata_id)=
        (met_hgjFdwJP-XZt, suggestion, met_--E5lqYffHN4) already exists.
STATEMENT:  INSERT INTO "metadata_to_metadata"
        ("from_metadata_id", "relation", "to_metadata_id")
        VALUES ($1, $2, $3) ON CONFLICT ("id") DO NOTHING

Impact

No data loss — the row already exists, so nothing is actually missing. But it generates a lot of noise and wasted database work:

• ~13,500 errors per refresh cycle
• 2 cycles in 3 days (26 Jul 03:00 and 27 Jul 17:00, local time)
• roughly 20% of that day's total log volume across my whole stack

I only noticed because a log-volume alert fired. Otherwise it is completely silent.

Suggested fix

Target the composite unique index instead of the primary key:

ON CONFLICT (from_metadata_id, relation, to_metadata_id) DO NOTHING

Environment

• Ryot: v10.4.0 (ghcr.io/ignisda/ryot:latest)
• Database: postgres:16-alpine
• Deployment: Docker, self-hosted

[dimon222]

This is probably continuation of this

Prior below PR it was actually causing fatal errors breaking the refresh loop. With this it causes silent errors (the app still works):
https://github.com/IgnisDa/ryot/pull/1778/changes

[TheQuietBofh]

Author here. Some data from my instance, in case it helps.

I no longer see the metadata_to_metadata duplicate key errors in my logs at all. The only duplicate key warnings left are for metadata-identifier-source-lot__unique-index, which is a different constraint (#899).

That matches what you describe: the failure went from fatal to silent. Which raises the question I can't answer from the outside — if it no longer logs anything, how do we tell whether the relations are still being dropped? Right now I have no way to know whether my metadata_to_metadata rows are complete or silently missing.

Happy to run any query against my Postgres if that helps narrow it down.

[IgnisDa]

if it no longer logs anything, how do we tell whether the relations are still being dropped?

The logs you posted in the original issue were from the postgres docker container, right?

Anyways, this error is harmless since it occurs only when Ryot is trying to create duplicate relations. Ideally, there should be a ON CONFLICT ... statement so that postgres does not log errors, but its harmless anyway.

Keeping this open and will fix it when I have some time. PRs also welcome :)

[TheQuietBofh]

Yes, those logs come from the Postgres container (postgres:16-alpine), not from the Ryot app container.

A fresher datapoint, in case it helps: over the last 48h my Postgres container logged 3,752 occurrences of duplicate key value violates unique constraint "metadata_to_metadata_from_metadata_id_relation_to_metada…", while the Ryot container logged zero.

So from the app side it is indeed silent now — the noise lives entirely in the database log. Fine by me to keep it open for the ON CONFLICT fix; my only practical concern is volume, since at that rate it buries anything else useful in the Postgres logs.