Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Shuttle live service is throwing "unique constraint error" (not a bug, but can be improved imo) #2167

Open
chetankashetti opened this issue Jul 12, 2024 · 5 comments
Open

Shuttle live service is throwing "unique constraint error" (not a bug, but can be improved imo) #2167

chetankashetti opened this issue Jul 12, 2024 · 5 comments
Labels
s-triage Needs to be reviewed, designed and prioritized

Comments

@chetankashetti
Copy link

What is the bug?
Shuttle live subscription will intermittently throws unique constraint error.
since its throwing error, it can cause application to crash. ideally if we can ignore that error that would be great.
image

How can it be reproduced?
If same message hash is processed twice this should appear.
@github-actions github-actions bot added the s-triage Needs to be reviewed, designed and prioritized label Jul 12, 2024
@tybook
Copy link
Contributor

tybook commented Jul 15, 2024 •
edited
Loading

Running into this as well with latest version (v0.5.3) of shuttle. I'm not sure this is "not a bug" like the original title here says, though.

Getting this on a near-identical fork of the example-app, with redis being completely flushed beforehand:

$ TABLES='profiles' yarn start start
yarn run v1.22.22
warning package.json: No license field
$ tsx src/app/app.ts start
[16:09:54.574] INFO (48515): Creating app connecting to: postgres://postgres:postgres@localhost:5432/farcaster, redis://default:@localhost:6379, 54.234.220.140:2283
[16:09:54.586] INFO (48515): Writing to tables: profiles
[16:09:54.586] INFO (48515): Starting shuttle
[16:09:54.702] INFO (48515): Migration "001_initial_migration" was executed successfully
[16:09:54.702] INFO (48515): Migration "002_casts" was executed successfully
[16:09:54.702] INFO (48515): Migration "003_profile" was executed successfully
[16:09:54.702] INFO (48515): Migrations up to date
[16:09:54.703] INFO (48515): Starting HubSubscriber shuttle
[16:09:54.719] INFO (48515): HubSubscriber shuttle connected to hub
[16:09:54.720] INFO (48515): HubSubscriber shuttle Found last hub event ID: 456197822128128
[16:09:54.722] INFO (48515): HubSubscriber shuttle subscribed to hub events (types [9,1,6,2,3], shard: 0/0)
[16:10:04.723] INFO (48515): Starting stream consumer
[16:10:04.818] INFO (48515): created message (merge) 0x3960248996caabc5dfeacb2f3cc770cbda50e55e (type 5)
[16:10:04.818] INFO (48515): created message (merge) 0x9d4f560f6dd860e20cbaf2dd72d1aaf56705b7d3 (type 5)
[16:10:04.818] INFO (48515): created message (merge) 0xe6f12b1ef9f72aaf12421c3643ad8a53c8ede6c6 (type 5)
[16:10:04.818] INFO (48515): created message (merge) 0xf605715d066af8307db59962ff4133e09c266044 (type 5)
<omitting many messages being processed>
[16:10:08.466] INFO (48515): created message (merge) 0x02dbcbca3208207509ad68beeb8e142c79a3ca29 (type 3)
[16:10:08.466] INFO (48515): created message (merge) 0x36b7299e3b9fb80fe320294594aee8fb5d6447ea (type 3)
[16:10:08.468] INFO (48515): created message (merge) 0xb44f880456be073426a73c6996f626d50a528793 (type 3)
[16:10:08.471] INFO (48515): created message (merge) 0x499a7a29a612ba27f6a0fccf1d784a55a398f85a (type 3)
[16:10:08.477] INFO (48515): created message (merge) 0x321e9f7a1eabb3e1190fcec5e6b699196d578000 (type 5)
[16:10:08.477] INFO (48515): created message (merge) 0xeea9730997e806adab94d8eaf086781ffa2aade6 (type 3)
error: duplicate key value violates unique constraint "messages_hash_unique"
    at /Users/tybook/neynar/nindexer/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at PostgresConnection.executeQuery (file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/dialect/postgres/postgres-driver.js:69:28)
    at file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:35:28
    at SingleConnectionProvider.#run (file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/driver/single-connection-provider.js:25:16)
    at PostgresConnection.executeQuery (file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/dialect/postgres/postgres-driver.js:88:41)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/query-executor/query-executor-base.js:35:28
    at SingleConnectionProvider.#run (file:///Users/tybook/neynar/nindexer/node_modules/kysely/dist/esm/driver/single-connection-provider.js:25:16)
[16:10:08.480] INFO (48515): created message (merge) 0xdf9a90955dd2ca6c848cc1735830ebf48b11a90f (type 3)
[16:10:08.480] INFO (48515): created message (merge) 0xfa9c90bc11b3ad7c9c9eb20319002dcc4979ba97 (type 1)
[16:10:08.480] INFO (48515): created message (merge) 0x23617ba0b4e85b67e1290d00e94368e18b8c8760 (type 3)
[16:10:08.484] INFO (48515): created message (merge) 0x59dadddd1b5d888f1bd773e99f1a1896862b70bc (type 5)
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

@chetankashetti
Copy link
Author

Interesting, I titled it "not a bug" because i observed in live shards. typically assumption i made was its possible for a message to be backfilled by workers before its being processed by live shards. when that happens it throws a unique constraint error.

The above scenario telling us something else? is shuttle processing message twice?

@tybook
Copy link
Contributor

tybook commented Jul 16, 2024

Interesting, I titled it "not a bug" because i observed in live shards. typically assumption i made was its possible for a message to be backfilled by workers before its being processed by live shards. when that happens it throws a unique constraint error.

The above scenario telling us something else? is shuttle processing message twice?

It appears a hub currently emits duplicate messages ~10% of the time, according to @BlinkyStitt 's work on a non-shuttle project of ours that also ingests directly from hubs. If the message hash at play here truly encompasses the entire message body, then it should be safe to change shuttle to just ignore any duplicate message hashes, I think. Will wait for someone with deeper fc/hubble knowledge to chime in, though.

@99darwin
Copy link

I'm experiencing the same, and it crashes my instance each time. I've added error-catching to avoid crashing, but I'm unsure how to handle the unique constraint error. Is it safe to upsert the duplicate data? Ignore?

@CassOnMars CassOnMars mentioned this issue Jul 17, 2024
Merged
4 tasks
CassOnMars added a commit that referenced this issue Jul 18, 2024
@CassOnMars
fix: on conflict criteria is ambiguous and cannot be used for upsert (#…
ee0947e 
…2197)

## Why is this change needed?

Shuttle defines the messages table with two unique constraints, one for
the hash, another for the triple of hash, fid and type. Postgres does
not allow multiple ON CONFLICT criteria, and ambiguous conflicts are
essentially surfaced as uniqueness violation failures in spite of ON
CONFLICT criteria given. This change alters the merge to instead attempt
insert, doing nothing on any conflict, then resolving with an update if
necessary and the original conflict resolution criteria is satisfied.

Addresses Issue #2167 

## Merge Checklist

_Choose all relevant options below by adding an `x` now or at any time
before submitting for review_

- [x] PR title adheres to the [conventional
commits](https://www.conventionalcommits.org/en/v1.0.0/) standard
- [x] PR has a
[changeset](https://github.com/farcasterxyz/hub-monorepo/blob/main/CONTRIBUTING.md#35-adding-changesets)
- [x] PR has been tagged with a change label(s) (i.e. documentation,
feature, bugfix, or chore)
- [x] PR includes
[documentation](https://github.com/farcasterxyz/hub-monorepo/blob/main/CONTRIBUTING.md#32-writing-docs)
if necessary.


<!-- start pr-codex -->

---

## PR-Codex overview
This PR updates the conflict resolution logic in `MessageProcessor` to
fix ambiguity issues during upsert operations.

### Detailed summary
- Updated conflict resolution logic to use `doNothing()` method instead
of complex conditions
- Improved handling of conflict criteria for upsert operations

> ✨ Ask PR-Codex anything about this PR by commenting with `/codex {your
question}`

<!-- end pr-codex -->
@tybook
Copy link
Contributor

tybook commented Jul 19, 2024

Can confirm this was fixed by #2197
Thanks @CassOnMars!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
s-triage Needs to be reviewed, designed and prioritized
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@tybook @chetankashetti @99darwin