Concurrent updates hang forever (v1.0.0)

[jgpruitt]

Bug Description

BM25 indexes created with v1.0.0 caused updates to hang forever and were unkillable.

Environment

• PostgreSQL version: 18.3
• pg_textsearch version: 1.0.0
• Operating system:

PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg22.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit

Steps to Reproduce

1. Create a database with memory engine at commit 87555fa3ac1d571f1ee286d988605364bfeb9194
2. Use PR 67 to load roughly 200k wikipedia articles into a table that has a BM25 index
3. The memory engine's async embedding worker will start issuing updates to set the embeddings on the rows. These updates hang forever, and pg_terminate_backend will not kill them. The only way to stop them is to bounce the database.
4. Upgrading pg_textsearch v1.1.0 AND rebuilding the BM25 indexes seems to fix the issue. Upgrading to v1.1.0 alone does not fix it.

Expected Behavior

I expected concurrent updates to a table with a BM25 index to complete in a reasonable amount of time rather than hanging forever. I expected pg_terminate_backend to kill the updates. It also seemed like statement_timeout and transaction_timeout were not honored.

Actual Behavior

Updates hung forever, even after the database client disconnected, and could not be killed without bouncing the database.

Additional Context

[tjgreen42]

I'm going to guess this is a duplicate of #355, which I just fixed for v1.2.0. The root cause there was that concurrent updates were causing docids to appear out of order in the memtable, and we were missing a sort step. We were also missing CHECK_FOR_INTERRUPT calls in a few search loops, making the queries unkillable.

[tjgreen42]

Tried to reproduce this against your exact recipe — couldn't trigger it. Sharing the methodology and negative result so you can sanity-check before we close anything out.

Setup

• memory-engine at commit 87555fa3 (the one you linked) + PR ERROR: invalid memory alloc request size 1874788361 #67 wikipedia importer rebased on top
• pg_textsearch v1.0.0 official Debian package, in Docker (pgvector/pgvector:0.8.2-pg18-trixie base, modified Dockerfile.postgres to pull v1.0.0 instead of v1.1.0). Verified mid-run: extversion=1.0.0, dpkg -l = pg-textsearch-postgresql-18 1.0.0-1, INFO: pg_textsearch v1.0.0 on CREATE EXTENSION.
• Bootstrapped a test identity directly via SQL (no OAuth) to skip the login dance
• Fake OpenAI-compatible /v1/embeddings endpoint serving deterministic 1536-dim vectors. The bug is in pg_textsearch's insert path, not in the embedding call, so the API can be faked without affecting the trigger condition. Worker still issues the same UPDATE memory SET embedding = $1::halfvec WHERE id = $2 AND embedding_version = $3.
• Host: macOS arm64 / Docker Desktop (so the actual postgres runs in a Linux VM)

Workload

• me wikipedia import simplewiki → 280,834 article memories imported cleanly (pages_scanned=553,618, matches your "200k articles" ballpark)
• Server restarted with WORKER_COUNT=8 (vs default 2) to compress wall-clock and increase concurrency on the embedding write-back path
• Workers ran for ~27 min until the queue fully drained (279,422 of 280,834 = 99.5% embedded)

What happened

• Three organic auto-spills triggered mid-workload (segments grew 1 → 2 → 3 → 4) — exactly the "concurrent appends + spill" sequence that fix: sort posting list when spilling memtable (root-cause MS MARCO bucket-8 hang) #360 identified as the corruption window
• 4 L0 segments at the end (37/35/40/42 MB), 1.23M total terms across 283k docs
• A watchdog snapshotted pg_stat_activity every 60s and was poised to dump sample//proc/$pid/stack on any backend stuck > 60s
• long_stuck > 60s = 0 on every single sample, the entire run. Active backends bounced in and out of LWLock: extension for sub-second waits — normal contention, never anything pathological.
• Ran 60 multi-term BMW queries (8-10 terms each, varied across various topical word lists) against the post-spill state at multiple checkpoints. Every single one returned in 86-283 ms. No spinning.
• pg_terminate_backend was never tested because nothing ever got stuck enough to need it.

So what's going on

I think there are a few possibilities. In rough order of how plausible they feel to me:

1. Linux ARM scheduling on your environment interleaves memtable writers more aggressively than macOS Docker's Linux VM does. The unsorted-posting-list race is genuinely timing-dependent — even the original BMW scan hangs uninterruptibly on bucket-7 MS MARCO queries (Benchmarks workflow timing out for weeks) #355/fix: sort posting list when spilling memtable (root-cause MS MARCO bucket-8 hang) #360 hang was intermittent.

2. tp_insert holds LW_EXCLUSIVE across tp_add_document_terms in v1.0.0, which should fully serialize concurrent appenders to the same posting list. If that's actually doing its job here, then the fix: sort posting list when spilling memtable (root-cause MS MARCO bucket-8 hang) #360 corruption needs a different path than concurrent tp_insert calls — maybe parallel index build during the import, vacuum, or replication. Worth double-checking the v1.0.0 code path on your end.

3. Your hang was data-shape-dependent — a particular memory's content update producing the specific posting-list interleaving needed.

What would unstick this

If you can dig up either of:

• The pg_stat_activity rows for the hung backends when you saw it (especially wait_event_type / wait_event / query_age), or
• A gdb thread-apply-all backtrace, or anything from /proc/$pid/stack for one of the hung pids

…that would immediately tell us whether the backend was spinning in BMW (the #357/#360 fix territory) or stuck somewhere else entirely. Right now I'd be guessing.

I'd suggest not closing this as a duplicate of #355/#357/#360 yet — the v1.2.0 fixes are real and good, but I haven't been able to confirm they actually address what you saw. Reopening if you can grab a stack next time it happens (assuming you've still got the v1.0.0 environment around).

[tjgreen42]

Tried a second angle that's much closer to PR #360's actual local-stress reproducer ("8-thread pgbench concurrent inserts of 4000 docs over 8 terms"). Still negative.

Setup

Same v1.0.0 container. Fresh database, fresh BM25 index. New pgbench script:

```sql
\set r1 random(1, 5)
... (eight \set r* random(1,5))
INSERT INTO docs (content) VALUES (
repeat('alpha ', :r1) || repeat('beta ', :r2) || ... || repeat('theta ', :r8)
);
```

Every doc is built from the same 8 terms — every concurrent INSERT appends to all 8 posting lists, so writers maximally contend on the same lists. This is the topology #360 says exposes the bug ("multiple backends interleave appends to the same posting list").

`pg_textsearch.memtable_spill_threshold = 200000` to push spills hard during the workload.

Workload

12 pgbench clients, 180s. Result:

• 1,524,906 INSERTs in 180s (8,471 tps), zero failed
• Segments fluctuated 3 → 11 in real time as auto-spills + L0→L1 merges happened (12 segments at the end: 5×L0 + 7×L1, totaling 75 MB)
• Watchdog: `stuck>60s = 0` every 30s sample, `max_age = 0s` every sample
• 7 BMW queries against various 4-8 term combinations against the post-workload state: 136-217ms, none stuck

So even with the exact topology #360 describes as the trigger condition (concurrent writers fighting for the same posting lists, frequent spills under that load), v1.0.0 doesn't produce a query-side hang here.

Where this leaves things

Three datapoints now, all negative on this environment (macOS arm64 + Docker Desktop, v1.0.0 confirmed):

1. Faithful memory-engine + simplewiki + 8 workers (~30 min, drained the queue, 60+ BMW queries) → no hang
2. Synthetic high-concurrency wikipedia UPDATEs with default thresholds → no hang
3. Narrow 8-term-vocab 12-thread pgbench INSERTs (textbook fix: sort posting list when spilling memtable (root-cause MS MARCO bucket-8 hang) #360 scenario) → no hang

All three featured organic auto-spills under concurrent writer pressure. None triggered the BMW infinite loop or any stuck-backend symptom.

I now seriously suspect the bug requires Linux scheduling (CFS) to interleave threads in a way that the macOS-hypervisor-hosted Linux VM doesn't. If you (or anyone) can re-run any of these on a real Linux host, that'd settle it. Short of that, I'd keep #370 open with these notes attached, and treat the v1.2.0 fixes as suspected-but-not-confirmed for this exact ticket.

[tjgreen42]

One more attempt — this one I think is the most diagnostic.

Built a 9-segment v1.0.0 BM25 index from 2,038,869 INSERTs across 12 concurrent pgbench clients (8,495 tps), using a Zipfian vocabulary (8 very-common terms + 50 rare + 200 very-rare) — i.e. the asymmetric distribution that should maximize BMW's pivot edge case from #357. Then directly compared BMW index-scan results vs sequential-scan standalone scoring on the same 8-term mixed-rarity query:

```sql
WITH bmw AS (
SELECT id, content <@> to_bm25query('alpha rare1 vrare1', 'docs_bm25') AS score
FROM docs
ORDER BY content <@> to_bm25query('alpha rare1 vrare1', 'docs_bm25')
LIMIT 100
),
seq AS (
SELECT id, content <@> to_bm25query('alpha rare1 vrare1') AS score
FROM docs
ORDER BY 2
LIMIT 100
)
SELECT 'bmw_only' AS diff, count() FROM (SELECT id FROM bmw WHERE id NOT IN (SELECT id FROM seq)) x
UNION ALL SELECT 'seq_only', count() FROM (SELECT id FROM seq WHERE id NOT IN (SELECT id FROM bmw)) x
UNION ALL SELECT 'score_diff', count(*) FROM (SELECT bmw.id FROM bmw JOIN seq USING (id) WHERE bmw.score <> seq.score) x;
```

Result:

```
diff | count
------------+-------
bmw_only | 0
seq_only | 0
score_diff | 0
```

BMW returned exactly the same top-100 IDs with exactly the same scores as the brute-force sequential scan. That means the segments aren't even structurally corrupt — not "corrupt but BMW happens to handle it." The unsorted-postings condition #360 fixed isn't manifesting at all in this environment, despite hitting every trigger condition we know of (concurrent writers + spill + Zipfian vocab + 12-way contention on the same posting lists).

By comparison, the #360 commit message reports the diagnostic on the original failing CI corpus found "904,174 inconsistencies" in 5.7M blocks. Mine: 0 inconsistencies, 0 query divergence.

So the bug really doesn't fire here, no matter what I throw at it. I agree it could still be timing-dependent rather than OS-dependent, but at this point I'd want a Linux-host repro before claiming the v1.2.0 fixes addressed this specific ticket — they may have addressed a real bug that John hit, but they may also have addressed a different bug that happens to be in the same code path. Both are possible from this evidence.

Closing out my investigation; happy to re-run if anyone reproduces it on a Linux host or shares stack traces from the original incident.

[tjgreen42]

Hi John — Claude here (TJ's been running me on this repro). Two things on your snippet:

What the INFO means

Benign. The first time any backend touches a pg_textsearch index whose in-memory state isn't already loaded in shared memory, it rebuilds posting lists from the on-disk docid pages (which are the durable record of what's in the memtable). That's what's logged — a "warming up" notice. "399 documents restored" = how many entries it walked from the docid chain back into the in-memory memtable for index OID 22989. Triggered most often by:

• Postmaster restart (the shared memtable cache is gone)
• First access to an index in a fresh backend after the cached state was reaped
• Crash recovery

It happens once per index per "warm-up event," then subsequent queries on that index don't log it.

But also — version mismatch worth flagging

That exact log line (Recovery complete for pg_textsearch index ...) only exists in v1.1.0+. In v1.0.0 the same message reads Recovery complete for tapir index ... (the old internal codename). The rename landed in PR #307 — "feat: fine-grained memtable locking for concurrent inserts" — on 2026-04-14, shipped in v1.1.0.

So whatever's emitting this line in your output is v1.1.0+, not v1.0.0. Possible you upgraded the binary at some point after the original hang and are now seeing the recovery log when querying indexes that were originally built under v1.0.0?

Why this might also explain my non-reproductions

PR #307's name is a smoking gun. Pre-#307 (i.e. v1.0.0), inserts hold the index-wide LW_EXCLUSIVE for the whole tp_add_document_terms call — which serializes all writers, full stop. Post-#307 (v1.1.0+), writers can append to different posting lists concurrently, which is the precondition #360 points at for the unsorted-postings corruption.

So my four prior repro attempts (all at v1.0.0) probably couldn't trigger the bug because v1.0.0's coarse lock makes the race impossible. The hang you saw may have actually been on v1.1.0, OR on v1.0.0 segments that v1.1.0 then touched. Either interpretation is consistent with your observation that "upgrading to v1.1.0 alone does not fix it" — the corrupt segments persist regardless of binary, but the new corruption that fed them required the v1.1.0 lock granularity.

Going to switch the repro to v1.1.0 next — TJ wants me to. That should actually trigger it if my reading is right.

[tjgreen42]

Update on the v1.1.0 attempt (TJ asked Claude to try this version after the version-mismatch flag).

Setup

Same memory-engine + simplewiki setup as before, only difference: pg_textsearch v1.1.0 .deb hot-swapped into the running container (verified extversion = 1.1.0, INFO: pg_textsearch v1.1.0 on CREATE EXTENSION). bootstrap.ts reverted to require ≥1.1.0 (matches the PR #67 era). 8 workers, fake OpenAI endpoint.

Notable difference from v1.0.0

v1.1.0 import + worker run truly concurrently in this run. On v1.0.0 the worker was effectively lock-starved by the importer and did almost nothing during ingest; on v1.1.0 the worker drained 170k embeddings while the importer was still going. Confirms PR #307 ("fine-grained memtable locking") changed the lock granularity as the name suggests — writers no longer fully serialize on the index-wide LWLock.

• v1.0.0 import time: ~28 min, worker idle during it
• v1.1.0 import time: ~12 min, worker concurrent throughout

That's the right setup for #360's bug to fire if it's going to fire here.

What actually happened

• 280,834 memories imported, 110k+ embeddings completed concurrently, total ~12 min
• Spills + L0→L1→L2 merges happened constantly during the workload (segments oscillated 1↔24 in real time)
• Long_stuck > 60s = 0 every sample. No backend ever stuck.
• BMW vs sequential-scan diff on 6 multi-term queries × 2 LIMITs (10 and 100) on the live partially-embedded data:

q=united states president government policy economic   diverge=0 (10), diverge=0 (100)
q=city town village population area country region     diverge=0 (10), diverge=0 (100)
q=music song singer band album record musician guitar  diverge=0 (10), diverge=0 (100)
q=war battle army military forces troops soldiers      diverge=0 (10), diverge=0 (100)
q=scientist research science theory experiment study   diverge=0 (10), diverge=0 (100)
q=alpha beta gamma rare1 vrare1                        diverge=0 (10), diverge=0 (100)

Score differences between BMW and seq scan are tiny (4th-5th decimal), inconsistent in direction (BMW sometimes better, sometimes worse) — looks like floating-point summation-order rounding, not corruption.

So even v1.1.0 doesn't reproduce in this environment

I now have four negative datapoints:

1. v1.0.0 simplewiki memory-engine — no hang, no corruption
2. v1.0.0 narrow 12-thread pgbench — no hang, no corruption
3. v1.1.0 simplewiki memory-engine — no hang, no corruption (this run)
4. v1.1.0 + v1.2.0 zipfian 12-thread pgbench — no hang, divergence is tie-breaking only

PR #360's verification reported "904,174 inconsistencies" on the original failing CI corpus. I'm getting 0 in every test. So either the bug requires something more specific than even "v1.1.0 fine-grained locking + concurrent writers + spill + diverse vocabulary," or there's something about the macOS Docker / Linux VM combination that suppresses it (I know TJ's skeptical of that — I am too at this point, but I've genuinely run out of variables to vary).

If you (or anyone with a Linux host handy) can run the exact same memory-engine setup, I'd be very curious whether this trivially reproduces there.

Closing my investigation again. Stack trace from the original incident remains the best hope of moving this forward.

[tjgreen42]

Pausing investigation here. Tally: tried v1.0.0 and v1.1.0 (the version your INFO log indicates you were actually running), with both the full memory-engine + simplewiki recipe and direct pgbench narrow/Zipfian-vocab workloads, plus deliberately tried to provoke OOM via low memory_limit. No hangs and no observable BMW corruption in any scenario — BMW results match sequential-scan results exactly (top-100 by score) on every test. PR #360's CI repro reported 904k inconsistencies on the original failing corpus; mine reports 0. Memory-pressure path is clean errors, not hangs.\n\nIf the bug recurs and someone can grab a pg_stat_activity row + a gdb thread apply all bt (or /proc/$pid/stack) for the hung backend, I'm happy to pick this up again. Without that, I can't tell whether v1.2.0's #357/#360 fixes addressed this ticket or merely a related one in the same code path.

[tjgreen42]

🎯 Reproduced. Took until I switched to the actual MS MARCO corpus that the original #355 CI hang was on.

The repro that worked

• pg_textsearch v1.0.0 (verified: `extversion=1.0.0`, .deb `pg-textsearch-postgresql-18 1.0.0-1`)
• macOS arm64 / Docker Desktop 28.0 / Linux VM (so OS-dependence is now ruled out — it does fire here)
• Full 8,841,823 MS MARCO passages loaded into staging
• 4-client pgbench INSERT-from-staging into a fresh BM25-indexed `passages` table (matches the bench workflow)
• After ~3.7M docs ingested (10 min wall), with segments oscillating L0↔L1↔L2 from constant merges, fired bucket-7 queries from `benchmark_queries.tsv`

The query that hit it: `"1x8 block with a sticker grand prix what set does it belong"` (8 tokens). Took 949 seconds wall-clock to die against a 15-second `statement_timeout` — 63× the configured timeout. Result was eventually `ERROR: canceling statement due to statement timeout`, exactly the BMW-spin-without-CFI fingerprint from #355.

When I re-ran the same query a minute later it returned in 71ms. Confirms #355's "intermittent / data-dependent" characterization — merges happening continuously in the background sort posting lists as a side effect, so the corruption window slides around. With `statement_timeout=15s`, in production this would manifest as queries that look "stuck" for many minutes per query, with `pg_terminate_backend` similarly delayed (interrupts only get checked when BMW exits the loop).

Why my earlier attempts missed it

• Wikipedia / synthetic narrow vocab / Zipfian — wrong term distribution. Almost no cases where `best_scorer` and the term that determined `min_block_end` are different terms with the right relative `max_score` ordering. The bucket-7 MS MARCO queries hit it because they have 7-8 unrelated tokens with vastly differing IDFs.
• 1M MS MARCO with the same 200 queries — clean. Needed 8.8M (or more time accumulating segments) for the bug to actually fire on a hot block.
• I think the macOS-Docker-suppressing-it hypothesis I'd been entertaining was wrong. It just needed the right corpus.

Currently running

Re-running all 200 bucket-7 + bucket-8 queries against the same live (still-being-inserted) state with 30s timeout, to characterize how often it fires. Will follow up with that distribution. Also want to grab a real stack trace next time it fires — I have `/proc/$pid/stack` and `gdb` access in the container.

Bottom line: #370's symptom is the BMW spin from #355/#360, and v1.2.0's fixes are aimed at the right thing. I owe you (and the v1.2.0 fixes) my earlier "may not be the real fix" framing — that was wrong, just needed the right repro.

[jgpruitt]

Was that a select or an insert that took 949 seconds wall-clock to die?

[tjgreen42]

Negative reproduction on main (commit 023c384, 1.3.0-dev — includes #357, #360, #365/#367, and #374's full memtable rewrite). Posting in case it helps close out the question of whether the v1.2.0/v1.3.0-dev fixes actually address #370.

Setup

• Real Linux host (the variable the prior macOS-Docker attempts kept getting stuck on): Ubuntu 24.04 / x86_64 / 16 cores / 32 GB RAM
• Dedicated Postgres 18.3 cluster on a custom port (to avoid contention with installcheck on the shared cluster), shared_buffers=1GB, work_mem=16MB, otherwise stock
• Full MS MARCO collection (8,841,823 passages) in staging
• 4-client pgbench INSERT-from-staging for 1800s into a fresh bm25 index, then bucket-7/8 query loop ran concurrently throughout

What ran

• Inserts: 3,521,135 docs in 1800s (1,956 tps, 0 failed). Slightly past the 3.7M point the v1.0.0 hang first fired at for TJ — close enough to be in the same regime
• Index ended at 15 segments across L0/L1/L2/L3 with continuous spills and merges throughout (L3 grew to a single 140 MB segment during the run, etc.)
• 280 query iterations × ~150 bucket-7/8 queries each ≈ 42,000 top-100 BMW queries fired concurrently with the writer, each with statement_timeout = 15s

Includes the exact query TJ tripped the v1.0.0 spin with — "1x8 block with a sticker grand prix what set does it belong".

Result

• 0 hangs, 0 timeouts, 0 errors across all 42k queries
• 3 outliers measured wall-clock 14.0–14.5s (still under timeout, all returned correct top-100). All 3 re-ran in 33–254 ms in isolation immediately after, so these look like cold-cache + concurrent-merge contention, not BMW spinning. A BMW spin would be reproducible and would stay near the timeout on repeated runs.
• BMW vs seq-scan top-100 validation on 6 bucket-7/8 queries (including TJ's original trigger): bmw_only=0, seq_only=0, score_diff=0 on every query. Zero divergence — segments are structurally consistent.

This matches what TJ's prior attempts found on main, but now ruling out the macOS-Docker hypothesis since I'm on a Linux host with the same MS MARCO corpus that did reliably fire the spin on v1.0.0.

What I think this means for #370

• The v1.0.0 hang John reported is the same BMW spin TJ reproduced — i.e. the BMW scan hangs uninterruptibly on bucket-7 MS MARCO queries (Benchmarks workflow timing out for weeks) #355/fix: BMW infinite loop in block_max_skip_advance (#355) #357/fix: sort posting list when spilling memtable (root-cause MS MARCO bucket-8 hang) #360 cluster, plus the additional block-skip safety from BMW reports under-scored results for some docs when K (default_limit) is much larger than the actual LIMIT #365/fix(bmw): include non-pivot term max scores in block-skip safety check (#365) #367
• All known root causes are fixed on main. Split SPILL / MERGE_LINKAGE: emit metapage / segment-header page changes via GenericXLog #374 additionally replaced the entire shared-memory memtable with an on-disk paged structure, removing whole classes of concurrency hazards
• No remaining hang or BMW spin visible against the workload that historically triggered it

If anyone can still hit a hang on main / v1.2.0+ with a fresh-built index, please share a pg_stat_activity snapshot + a /proc/$pid/stack or gdb thread apply all bt — that's still the most useful artifact for any residual bug in the same code path. Otherwise this looks resolved.

To John's earlier question: the 949s wall-clock event in TJ's reproducer was a SELECT (an ORDER BY ... LIMIT BMW index scan), not an INSERT/UPDATE — so the original UPDATE-hangs symptom was almost certainly that the embedding worker's UPDATEs were waiting on, or interleaved with, a SELECT (or autovacuum scan / similar reader) stuck in BMW. Once BMW is unkillable, anything sharing locks with it inherits the hang.