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

[chetankashetti]

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.

How can it be reproduced? If same message hash is processed twice this should appear.

[tybook]

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]

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]

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]

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?

[tybook]

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