Batch INSERT Replication Fails for Sizes Divisible by 100

[blj]

batch_insert_test.exs.txt

Versions

• Client: Plain HTTP (integration test using Electric.Client)
• Electric: sync-service (Elixir backend) - current main branch

Bug Description

Batch INSERT statements with row counts exactly divisible by 100 (100, 200, 300, 400, 500, etc.) fail to replicate. The database insert succeeds, but zero messages are received by clients instead of the expected number of change messages.

Affected Batch Sizes

• ✅ Works: 25, 50, 75, 125, 150, 175, 225, 250, 275, 325, 350, 375, 425, 450, 475
• ❌ Fails: 100, 200, 300, 400, 500 (and presumably 600, 700, etc.)

Expected Behavior

When executing a batch INSERT statement with N rows (e.g., INSERT INTO items VALUES (...), (...), ... with 100 rows), the Electric sync service should:

1. Successfully insert all N rows into the database (this works)
2. Replicate all N change messages to subscribed clients (this fails for N divisible by 100)
3. Clients should receive N ChangeMessage events with operation: :insert

Steps to Reproduce

1. Set up a table and Electric shape subscription
2. Execute a batch INSERT with exactly 100 rows (or 200, 300, etc.)
3. Wait for replication
4. Observe that 0 messages are received instead of 100

Test Case

See attached test file: packages/sync-service/test/integration/batch_insert_test.exs (Please remove the extension .txt as github won't let me attach a .exs file`

• Test: "receives all rows from batch insert (100 rows)"
• Expected: 100 messages
• Actual: 0 messages

[blj]

Why this patch is needed

When a transaction's changes reach exactly max_batch_size (100), MessageConverter.maybe_flush/1 flushes a fragment with 100 changes and creates a new empty fragment. When the commit arrives, the commit fragment is created with last_log_offset = state.last_log_offset, which is the same offset as the flushed fragment.

ShapeLogCollector.handle_txn_fragment/2 has a guard clause that drops fragments where last_log_offset <= last_processed_offset. Since the commit fragment had the same offset as the already-processed flushed fragment, it was dropped as "already processed", preventing the transaction from completing.

The fix

For empty commit fragments (after a flush), use msg.end_lsn (the commit's final LSN) instead of state.last_log_offset to set last_log_offset. This ensures the commit fragment has a unique offset greater than any previous fragment, so it won't be dropped.

Verification

• All batch insert tests pass, including sizes that are multiples of 100 (100, 200, 300, 400, 500)
• The test suite now references the configured batch size constant to catch similar issues if the batch size changes

This is a minimal change that only affects the offset calculation for empty commit fragments, leaving all other behavior unchanged.

Fixes #3726

fix-issue-3726.patch

[blj]

I am looking at this with fresh eyes this morning, I think this problem can affect any transaction that contains precisely divisible by 100 number of statements. I am modifying the test to confirm and some have some mixed bag of changes:

• 50 INSERTs + 50 UPDATEs = 100 changes
• 33 INSERTs + 33 UPDATEs + 34 DELETEs = 100 changes
• 25 INSERTs + 25 UPDATEs + 25 DELETEs + 25 INSERTs = 100 changes
• 200 INSERTS
• 300 UPDATES
• 100 DELETES

[KyleAMathews]

Haha wow that'd be amazing. Can you try to make a pr with just a failing test?

[blj]

Yes sure.

Haha wow that'd be amazing. Can you try to make a pr with just a failing test?

[blj]

I am quite certain this is the issue of stalling mentioned here https://tanstack.com/db/latest/docs/collections/electric-collection#common-issue-awaittxid-stalls-or-times-out