Massive sync resolves reply parents out of chain order around delete/re-create: re-created replies dropped, replies attached to deleted parent rows

Rewritten 2026-10-26: the original diagnosis covered only dropped re-created replies (effect A). Reading Step 7 against shed14's data shows a second, silent effect (B): replies attached to the parent's deleted row. The cause is the same, so both are in scope here.

Problem and intended result

Massive sync handles posts that are deleted and re-created within one batch out of chain order (hive/db/sql_scripts/massive_sync.sql, Steps 3-5 and Step 7):

  • Steps 3-5 insert every first creation in the batch before Step 7 replays any delete.
  • Step 7a applies every "simple" delete (a pair that is not re-created later in the batch) as one set-based UPDATE.
  • Step 7b replays the delete -> re-create cycles in a loop ORDER BY (author || '/' || permlink), s.id: each pair's own ops are in chain order, but the pairs are processed alphabetically, not interleaved by s.id.
  • A comment's parent is resolved in process_hive_post_operation (hive/db/sql_scripts/hive_post_operations.sql) as the parent pair's row with counter_deleted = 0 at replay time.

So a reply's parent is resolved against whatever state the replay order happens to leave, not the parent's state at the reply's own op. Two observable results:

A. Re-created replies are dropped. When a reply is re-created (Step 7b) at a point where its parent was live on chain, but hivemind has already marked that parent deleted (by Step 7a, or by an alphabetically earlier pair in 7b), the parent lookup finds no row and the INSERT ... SELECT inserts nothing. No error, no log. Measured on a 1.28.8 node synced from scratch: 1,593 comments on 52 days (2016-08 to 2021); posts are never affected and hivemind never has more comments than the chain.

B. Replies are attached to the parent's deleted row. When a reply is created after its parent was deleted and re-created earlier in the same batch, hivemind resolves the parent while the parent's old row is still live (the reply is a first creation in Steps 3-5, or its pair sorts before the parent's in 7b). The parent's replayed delete then marks that row deleted, and the re-create inserts a new row, so the reply ends up with parent_id (and root_id, depth, community and category inherited from it) pointing at a deleted row. hived rejects deleting a comment that has replies, so on a correct node no live reply can have a deleted parent row. On shed14 (hivemind massive-synced from scratch, now at head 111.1M) there are 376 such replies: every one was created after its parent's live row, and within 1,000 blocks of the parent's delete, i.e. in the same batch. By year: 2016: 2, 2017: 28, 2018: 66, 2019: 92, 2020: 142, 2021: 31, 2022: 10, 2023: 1, 2025: 4. Such replies are missing from their post's discussion, from its children count, and from community attribution. Example:

reply created parent parent's old row: created / deleted parent's live row created
@worldmappin/wmp1762622520388 101,008,704 @ulisg/a-beautiful-tour-to-discover-the-cristo-rey-eng-esp 101,008,494 / 101,008,518 101,008,699
@enlace/resp-una-manana-de-compras-a-morning-of-shopping-esp-eng 99,889,791 @stefany0720/una-manana-de-compras-a-morning-of-shopping-esp-eng 99,886,765 / 99,889,122 99,889,160
@hivebuzz/notify-1751798107 97,404,692 @nevenaix/crete-greece 97,404,252 / 97,404,264 97,404,471

(The first example is a plain first creation, ulisg sorts before worldmappin: Steps 3-5 alone produce B.)

Detection query (B):

SELECT count(*)
FROM hivemind_app.hive_posts c
JOIN hivemind_app.hive_posts p ON p.id = c.parent_id
WHERE c.counter_deleted = 0 AND c.depth > 0 AND p.counter_deleted > 0;

Intended result: massive sync resolves each comment's parent as of that comment's own op in chain order, exactly as live (block-by-block) sync does: a reply is never dropped while its parent was live on chain, and is always attached to the parent row that was live at the reply's op. The set-based character of massive sync stays (Step 7a exists because a per-row loop over ~783K deletes was too slow); do not replace it with a per-op loop over the whole batch.

Ground truth (effect A)

hived's post_count counts every creation the chain accepted. For the two accounts behind the largest gaps:

account hived post_count creations replayed from HAF ops (posts + comments) hivemind hive_posts rows (posts + comments)
crystalan 19,950 19,950 (10,577 + 9,373) 19,250 (10,577 + 8,673)
crystalliu 45,025 45,025 (28,189 + 16,836) 44,510 (28,189 + 16,321)

The replayed creation counts come from haf_stats, which re-implements hived's create-vs-edit and delete decisions op by op.

Example (effect A)

@crystalan on 2021-04-14 (blocks 53,004,499–53,033,255): hivemind is 14 comments short for that day. The dropped replies sit in chains in which every link is itself in a delete/re-create loop:

  • qri82s → qri7xk → qri78j → qri75w;
  • qrk2l9 and qrk36e → crystal-an-qrk2az (the parent was deleted 9 times in the window).

qrk2l9, for example, has 6 creations in chain order:

1@53020568 17@53020586 1@53020714 17@53020735 1@53021065 17@53021068 1@53021069 17@53021071 1@53021071 17@53021076 1@53021076 17@53021090

hivemind has 2 rows for it, block_num_created 53020568 and 53021065.

Every affected day (effect A)

Comments created (chain) minus hivemind hive_posts rows with depth > 0, grouped by the day of block_num_created:

day missing comments
2016-08-10 1
2018-01-19 1
2018-01-22 1
2018-01-23 2
2018-09-27 2
2019-06-30 3
2019-07-04 4
2019-08-10 7
2019-08-11 4
2019-08-23 1
2020-01-15 107
2020-01-16 1
2020-01-17 12
2020-01-18 78
2020-01-21 69
2020-01-29 66
2020-01-30 111
2020-02-01 10
2020-02-02 12
2020-02-07 10
2020-02-09 10
2020-02-17 6
2020-02-20 35
2020-02-27 5
2020-04-27 18
2021-02-02 65
2021-02-03 182
2021-02-11 3
2021-02-16 32
2021-02-17 56
2021-04-03 22
2021-04-07 65
2021-04-08 2
2021-04-09 45
2021-04-13 50
2021-04-14 14
2021-04-15 7
2021-04-16 6
2021-04-17 73
2021-04-18 5
2021-04-20 21
2021-04-23 17
2021-04-28 3
2021-05-03 17
2021-05-12 15
2021-07-03 57
2021-07-12 160
2021-07-18 11
2021-07-19 5
2021-07-20 74
2021-07-23 8
2021-07-31 2

No day in 2022–2026 is affected, and no day anywhere has a post difference.

Acceptance criteria

  • A massive-sync test over a crafted batch (mock block data under mock_data/block_data/comment_op/, or an SQL-level test that drives the massive-sync function over staged ops) covers: (1) a reply re-created while its parent is live, where the parent is later deleted in the same batch (simple delete, and a cycle pair sorting before the reply's); (2) a reply first-created after its parent's delete -> re-create in the same batch; (3) a reply in a cycle whose parent pair sorts after it. Afterwards, every reply the chain created exists in hive_posts, and every live reply's parent_id is the parent row live at its op (so the detection query above returns 0). The test fails on the current massive_sync.sql and passes after the fix.
  • Live (non-massive) sync results for those ops are unchanged, and the massive-sync and live-sync results for the crafted batch are identical.
  • Massive sync of the existing CI range takes no more than 10% longer than before (state the before/after timings in the commit message).

Integration points

  • hive/db/sql_scripts/massive_sync.sql -- Steps 3-5 parent resolution for comments whose parent pair has a delete in the batch, Step 7a/7b ordering, Step 7c/7d follow-ups that key on the re-created rows.
  • hive/db/sql_scripts/hive_post_operations.sql -- process_hive_post_operation's parent lookup, if the fix resolves the parent as of the op rather than counter_deleted = 0.
  • mock_data/block_data/comment_op/ (or the SQL test harness) -- the crafted delete/re-create batch.

Existing nodes need a hivemind resync to repair already-stored data; that is an operator step, not part of this change.

Found while building a production cross-check between haf_stats and hivemind post/comment counts (haf_stats#63 (closed)).

Edited by Dan Notestein