bug(replicator): error: insert or update on table "messages" violates foreign key constraint "messages_fid_foreign"

[saez0pub]

What is the bug?
I have a lot of logs with foreign key contraint errors.
If this is not an error, it would be nice to not show them as error.

How can it be reproduced? (optional)
Fresh replicator sync

Hubble version: 1.10.5 (latest)
replicator version: 0.3.2 (latest)

Delayed queue has 17798 entries

Additional context (optional)
Here are some logs (I extracted between 08:47:24.544 and 09:02:33.491 UTC time). Unicode are already in issue #1753

  15290 Error processing message 0xNNNN (fid XXX): error: unsupported Unicode escape sequence
  15201 Job BackfillFidVerifications XXXXXX failed: unsupported Unicode escape sequence: error: unsupported Unicode escape sequence
   1358 Job ProcessHubEvent XXXXXX failed: or update on table "messages" violates foreign key constraint "messages_fid_foreign": error: insert or update on table "messages" violates foreign key constraint "messages_fid_foreign"
     89 Job ProcessHubEvent XXXXXX failed: unsupported Unicode escape sequence: error: unsupported Unicode escape sequence
      5 Job ProcessHubEvent XXXXXX failed: or update on table "signers" violates foreign key constraint "signers_fid_foreign": error: insert or update on table "signers" violates foreign key constraint "signers_fid_foreign"
      4 Job BackfillFidCasts XXXXXX failed: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign": error: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign"
      2 Job BackfillFidReactions XXXXXX failed: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign": error: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign"
      1 StatsD Error
      1 Job BackfillFidUserData XXXXXX failed: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign": error: insert or update on table "messages" violates foreign key constraint "messages_signer_fid_foreign"

Here is a sample:

[09:02:31.869] ERROR (1): Job ProcessHubEvent 12569693 failed: insert or update on table "messages" violates foreign key constraint "messages_fid_foreign": error: insert or update on table "messages" violates foreign key constraint "messages_fid_foreign"
    at /home/node/app/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/home/node/app/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async /home/node/app/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async #run (/home/node/app/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at PostgresConnection.executeQuery (/home/node/app/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:91:69)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /home/node/app/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async #run (/home/node/app/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at execute (/home/node/app/apps/replicator/build/db.js:129:50)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async storeMessage (/home/node/app/apps/replicator/build/processors/index.js:200:5)
    at async processMessage (/home/node/app/apps/replicator/build/processors/index.js:70:5)
    at async mergeMessage (/home/node/app/apps/replicator/build/processors/index.js:52:5)
    at async /home/node/app/apps/replicator/build/processors/index.js:292:9
    at async /home/node/app/apps/replicator/build/db.js:156:20
    at async /home/node/app/node_modules/kysely/dist/cjs/kysely.js:418:32
    at async DefaultConnectionProvider.provideConnection (/home/node/app/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:12:20)
    at async executeTx (/home/node/app/apps/replicator/build/db.js:155:16)
insert into
  "messages" (
    "created_at",
    "updated_at",
    "fid",
    "type",
    "timestamp",
    "hash",
    "hash_scheme",
    "signature",
    "signature_scheme",
    "signer",
    "raw",
    "deleted_at",
    "pruned_at",
    "revoked_at",
    "body"
  )
values
  (
    '2024-02-29T09:02:31.859Z',
    '2024-02-29T09:02:31.859Z',
    375308,
    5,
    '2024-02-29T09:01:28.000Z',
    '\x6870e114e697e0f8b44ba6c316ecd8c4cf60b9d4',
    1,
    '\xad36b6c0106f0757933f3d79934b5fd53cee0d835bd8381aff0763a9be5fd7ff5c7a8aff0fe605aa7d33e9025069ea4af0e284cebc44de115491513095a64a0b',
    1,
    '\x38b3b05dedba61704724b8e1a3e78bf034b8463d49731f85a7b14d2806fd7a16',
    '\x0a1b0805108cf41618e8c3c72f2001720c0a06666f6c6c6f7718e9f51612146870e114e697e0f8b44ba6c316ecd8c4cf60b9d418012240ad36b6c0106f0757933f3d79934b5fd53cee0d835bd8381aff0763a9be5fd7ff5c7a8aff0fe605aa7d33e9025069ea4af0e284cebc44de115491513095a64a0b2801322038b3b05dedba61704724b8e1a3e78bf034b8463d49731f85a7b14d2806fd7a16',
    null,
    null,
    null,
    '{"type":"follow","targetFid":375529}'
  )
on conflict ("hash") do
update
set
  "updated_at" = '2024-02-29T09:02:31.859Z',
  "signature" = "excluded"."signature",
  "signature_scheme" = "excluded"."signature_scheme",
  "signer" = "excluded"."signer",
  "deleted_at" = null,
  "pruned_at" = null,
  "revoked_at" = null
where
  (
    "excluded"."signature" != "messages"."signature"
    or "excluded"."signature_scheme" != "messages"."signature_scheme"
    or "excluded"."signer" != "messages"."signer"
    or "excluded"."deleted_at" is distinct from "messages"."deleted_at"
    or "excluded"."pruned_at" is distinct from "messages"."pruned_at"
    or "excluded"."revoked_at" is distinct from "messages"."revoked_at"
  )
returning
  "hash",
  "updated_at",
  "created_at"
    at executeTx (/home/node/app/apps/replicator/build/db.js:161:50)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async processMergeMessageHubEvent (/home/node/app/apps/replicator/build/processors/index.js:291:5)
    at async processHubEvent (/home/node/app/apps/replicator/build/processors/index.js:250:13)
    at async Object.run (/home/node/app/apps/replicator/build/jobs/processHubEvent.js:11:9)
    at async /home/node/app/node_modules/bullmq/dist/cjs/classes/child-processor.js:69:33

[bitspresso]

Linked: #1696, #1556.
Seems solvable by simple constraints removal.

[saez0pub]

I would say that if constraints are not respected, something odd happened. Removing constraint just remove control of data integrity.

I resynced from scratch my db.

I still have theses errors about constraints.

looking at this constraint:

constraint messages_fid_foreign
            references public.fids
            on delete cascade

It means that every message should have a corresponding fid in the fids table.
These message events are missing a fid in the database.
If I remove the constraint, I would have inconsistencies on joins between messages and other tables.

[bitspresso]

I would say that if constraints are not respected, something odd happened. Removing constraint just remove control of data integrity.

I resynced from scratch my db.

I still have theses errors about constraints.

looking at this constraint:

constraint messages_fid_foreign
            references public.fids
            on delete cascade

It means that every message should have a corresponding fid in the fids table. These message events are missing a fid in the database. If I remove the constraint, I would have inconsistencies on joins between messages and other tables.

Indeed, I still experience same issue 3rd time (syncing from scratch as well) and agree that the linked issues provide rather a workaround and do not solve the actual problem.

[saez0pub]

One of the reasons we have some inconsistencies can be #1809

[sds]

Calling out that this comment might help shed some light on what is being observed here. Appreciate your patience as we work through identifying a path forward.

[sds]

Closing as we recommend using the Shuttle project instead of the old replicator (which has multiple issues).