[Bug]: Unable to vote due to apparent database conflict: "there is no unique or exclusion constraint matching the ON CONFLICT specification"

[csolisr]

Requirements

• Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
• Did you check to see if this issue already exists?
• Is this only a single bug? Do not put multiple bugs in one issue.
• Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Summary

When attempting to vote on any posts, my self-hosted site is unable to store the vote. Checking on the logs I get an error message that states there is no unique or exclusion constraint matching the ON CONFLICT specification. This points towards a problem with the database, but I'm not sure if it can be solved by rebuilding some index, or by fixing the upstream handling of duplicate data.

Steps to Reproduce

1. Log into Lemmy
2. Attempt to upvote any post

Technical Details

I'm getting a constant barrage of errors similar to the one below:

WARN Error encountered while processing the incoming HTTP request: lemmy_server::root_span_builder: there is no unique or exclusion constraint matching the ON CONFLICT specification
   0: lemmy_apub::activities::voting::vote_post
             at crates/apub/src/activities/voting/mod.rs:126
   1: lemmy_apub::activities::voting::vote::receive
             at crates/apub/src/activities/voting/vote.rs:71
   2: lemmy_apub::activities::community::announce::receive
             at crates/apub/src/activities/community/announce.rs:141
   3: lemmy_server::root_span_builder::HTTP request
           with http.method=POST http.scheme="http" http.host=communities.azkware.net http.target=/inbox otel.kind="server" request_id=7e024b6a-3e4c-47b6-984d-0e2f04a52602 http.status_code=400 otel.status_code="OK"
             at src/root_span_builder.rs:16
LemmyError { message: None, inner: there is no unique or exclusion constraint matching the ON CONFLICT specification, context: SpanTrace [{ target: "lemmy_apub::activities::voting", name: "vote_post", file: "crates/apub/src/activities/voting/mod.rs", line: 126 }, { target: "lemmy_apub::activities::voting::vote", name: "receive", file: "crates/apub/src/activities/voting/vote.rs", line: 71 }, { target: "lemmy_apub::activities::community::announce", name: "receive", file: "crates/apub/src/activities/community/announce.rs", line: 141 }, { target: "lemmy_server::root_span_builder", name: "HTTP request", fields: "\u{1b}[3mhttp.method\u{1b}[0m\u{1b}[2m=\u{1b}[0mPOST \u{1b}[3mhttp.scheme\u{1b}[0m\u{1b}[2m=\u{1b}[0m\"http\" \u{1b}[3mhttp.host\u{1b}[0m\u{1b}[2m=\u{1b}[0mcommunities.azkware.net \u{1b}[3mhttp.target\u{1b}[0m\u{1b}[2m=\u{1b}[0m/inbox \u{1b}[3motel.kind\u{1b}[0m\u{1b}[2m=\u{1b}[0m\"server\" \u{1b}[3mrequest_id\u{1b}[0m\u{1b}[2m=\u{1b}[0m7e024b6a-3e4c-47b6-984d-0e2f04a52602 \u{1b}[3mhttp.status_code\u{1b}[0m\u{1b}[2m=\u{1b}[0m400 \u{1b}[3motel.status_code\u{1b}[0m\u{1b}[2m=\u{1b}[0m\"OK\"", file: "src/root_span_builder.rs", line: 16 }] }

Version

0.18.2

Lemmy Instance URL

communities.azkware.net

[csolisr]

Some more help, from the Postgres logs:

2023-07-20 11:05:34.332 CST [254823] lemmy@lemmy ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
2023-07-20 11:05:34.332 CST [254823] lemmy@lemmy STATEMENT:  INSERT INTO "post_like" ("post_id", "person_id", "score") VALUES ($1, $2, $3) ON CONFLICT ("post_id", "person_id") DO UPDATE SET "post_id" = $4, "person_id" = $5, "score" = $6 RETURNING "post_like"."id", "post_like"."post_id", "post_like"."person_id", "post_like"."score", "post_like"."published"
^C

[Nutomic]

Looks like your database is misconfigured, try to set it up again from scratch.