fk_memory_links_to_unit_id_memory_units violation under sustained concurrent retain (mirror of #1795)

[ekovshilovsky]

fk_memory_links_to_unit_id_memory_units violation under sustained concurrent retain (mirror of #1795)

Summary

The from_unit_id side of the memory_links → memory_units foreign-key race was fixed in #1805 (closes #1795). The to_unit_id side is the mirror case and still races under the same load pattern. Hitting it at v0.7.1 reproducibly during multi-bank batch ingest.

Environment

• Hindsight: v0.7.1 (self-hosted, ghcr.io/vectorize-io/hindsight:latest digest as of 2026-05-28)
• Embedded pg0 Postgres 18.1.0
• All known earlier fixes confirmed present:
  • PR fix(retain): defer memory_links → memory_units FKs to break cascade deadlock #1398 (deferred FK) — error surfaces at COMMIT, not INSERT (so this isn't the pre-1398 race)
  • PR fix(retain): keep oversized items in one async child to stop FK race (#1795) #1805 / Issue 0.7.0: oversized retain split can enqueue concurrent chunks for same document, causing worker thread exhaustion and ANN FK failures #1795 (from_unit_id orphan fix) — from_unit_id no longer races
  • 3-phase retain pipeline (v0.5.0)
• Single tenant, four banks active (writes scoped per bank, no cross-bank writes)

Observed behavior

A single nightly process posts to /v1/default/banks/<bank>/memories ~225 times in <1 second across two banks (batched as one async retain per call, async: true, batch_size: 1). On most days, 1–5 of those operations fail in Hindsight's queue with the FK violation below; the rest commit fine. The failed ops remain in async_operations with status='failed' and never auto-retry.

Error (verbatim from async_operations.error_message)

insert or update on table "memory_links" violates foreign key constraint
"fk_memory_links_to_unit_id_memory_units"
DETAIL:  Key (to_unit_id)=(<uuid>) is not present in table "memory_units".

Traceback (most recent call last):
  File "/app/api/hindsight_api/engine/memory_engine.py", line 1337, in execute_task
    await self._handle_batch_retain(task_dict)
  File "/app/api/hindsight_api/engine/memory_engine.py", line 917, in _handle_batch_retain
    await self.retain_batch_async(
  File "/app/api/hindsight_api/engine/memory_engine.py", line 2669, in retain_batch_async
    result, total_usage, total_processed_content_tokens = await self._retain_batch_async_internal(...)
  File "/app/api/hindsight_api/engine/memory_engine.py", line 2787, in _retain_batch_async_internal
    return await orchestrator.retain_batch(...)
  File "/app/api/hindsight_api/engine/retain/orchestrator.py", line 656, in retain_batch
    return await _streaming_retain_batch(...)
  File "/app/api/hindsight_api/engine/retain/orchestrator.py", line 1329, in _streaming_retain_batch
    await asyncio.gather(_llm_producer(), _db_consumer())
  File "/app/api/hindsight_api/engine/retain/orchestrator.py", line 983, in _db_consumer
    await _process_db_batch(...)
  File "/app/api/hindsight_api/engine/retain/orchestrator.py", line 1278, in _process_db_batch
    await _run_mini_batch_db_work()
  File "/app/api/hindsight_api/engine/retain/orchestrator.py", line 1151, in _run_mini_batch_db_work
    async with conn.transaction():
  File "/usr/local/lib/python3.11/contextlib.py", line 217, in __aexit__
    await anext(self.gen)
  File "/app/api/hindsight_api/engine/db/postgresql.py", line 32, in transaction
    async with self._conn.transaction():
  File "/app/api/.venv/lib/python3.11/site-packages/asyncpg/transaction.py", line 91, in __aexit__
    await self.__commit()
asyncpg.exceptions.ForeignKeyViolationError

Also observed: failed graph_maintenance operations with the same fk_memory_links_to_unit_id_memory_units violation, indicating the post-link maintenance phase can also reference units that aren't yet visible.

Reproduction shape

1. Two banks (any two — distinct bank_ids, isolated writes)
2. From one upstream process, fire ~225 POST /v1/default/banks/<bank>/memories calls within ~1 second, async: true, single-item batches
3. Watch SELECT * FROM async_operations WHERE status='failed' over the next minute — expect 1–5 rows with the FK violation above

Hypothesis

The to_unit_id FK on memory_links is constraint-checked at COMMIT (per PR #1398's deferred-FK design). Under sustained concurrent load, the pre-resolve / insert / post-link 3-phase pipeline (v0.5.0) can produce a transaction whose link insert references a unit ID that another concurrent transaction hasn't yet committed. PR #1805 caught this for from_unit_id (the "earlier" side of the link in the resolve order); the to_unit_id side appears uncovered.

Local workaround in place

Sleeping ~50ms between async-retain HTTP calls on our side reduces effective concurrency enough to make the race rare-to-zero in our traffic. This is a UX patch, not a fix — the upstream race still exists for any caller doing burst writes.

Related

• 0.7.0: oversized retain split can enqueue concurrent chunks for same document, causing worker thread exhaustion and ANN FK failures #1795 / PR fix(retain): keep oversized items in one async child to stop FK race (#1795) #1805 — from_unit_id mirror, fixed pre-v0.7.0
• PR fix(retain): defer memory_links → memory_units FKs to break cascade deadlock #1398 — deferred-FK constraint changes (provides the COMMIT-time surfacing path)
• v0.5.0 — 3-phase retain pipeline
• batch_retain can pass zero-length embeddings to pgvector (vector(384) vs vector(0)) #1669 — concurrent-load edge case in batch_retain (different symptom, similar class)