retain: ForeignKeyViolationError from the delete_document race is classified non-retryable (#980 blanket), dropping retains at retry_count=0

Environment

• hindsight-api 0.9.1, PostgreSQL backend, pgvector, single dataplane process
• Verified the same code is present in 0.9.2 and 0.10.0 (latest)

Summary

_is_non_retryable_task_error (engine/memory_engine.py) treats every asyncpg.IntegrityConstraintViolationError as deterministic. That blanket was introduced by #980, whose motivating case was UniqueViolationError on pk_chunks — genuinely deterministic.

But ForeignKeyViolationError raised by the retain replacement path is not deterministic: it is a concurrency race whose retry succeeds. Because the blanket catches it, those retains go terminal at retry_count = 0 and the conversation content is dropped with no further attempt.

The race

delete_document documents the race itself:

"Called when a document is replaced, so it races the replacement's writes: it must remove only what was written before this call, never the facts arriving moments later"

When two retains for the same document_id overlap, one runs DELETE FROM memory_units WHERE document_id = $1 AND bank_id = $2 while the other inserts unit_entities rows referencing units in that set. PostgreSQL's RI check on the parent delete then raises, despite the constraint being ON DELETE CASCADE (the cascade removes children visible at delete time; a concurrently committed child still trips the final check):

ForeignKeyViolationError: update or delete on table "memory_units" violates
foreign key constraint "fk_unit_entities_unit_id_memory_units" on table "unit_entities"
DETAIL: Key (id)=(...) is still referenced from table "unit_entities".

Overlap is easy to reach whenever extraction is slow: retains for one long-lived append-mode document queue up behind each other. In our case the same condition also drove the [delta] ... was modified by concurrent request — aborting delta, falling back to full retain path, which widens the delete window further.

Evidence that it is transient, not deterministic

From one deployment's async_operations:

LLM failures retry normally (638 with retry_count > 0); the FK class goes terminal on first contact.

Recovery confirms transience:

• one operation retried by hand via POST /operations/{id}/retry → completed on the first attempt
• a controlled drain of the accumulated backlog (382 failed retains across 73 documents, never two of the same document in flight) is running at 56 recovered, 0 failures so far

Nothing about the data changed between the terminal failure and the successful retry — only the concurrency.

Impact

Silent memory loss. 327 retains in this deployment were discarded without a single retry; the transcripts they carried never reached the bank, and nothing surfaces to the caller because the retain API is async: true. The backlog is recoverable only by manually driving /operations/{id}/retry.

Suggested fix

Narrow the classification so a FK violation is retryable while the genuinely deterministic siblings stay terminal:

def _is_non_retryable_task_error(e: Exception) -> bool:
    """Classify deterministic task failures that should skip worker retry."""
    if isinstance(e, asyncpg.exceptions.ForeignKeyViolationError):
        # Concurrency race (see delete_document), not bad data: retry succeeds.
        return False
    return (
        isinstance(e, asyncpg.exceptions.IntegrityConstraintViolationError)
        or _is_oracledb_integrity_error(e)
        or _is_invalid_embedding_dimension_error(e)
    )

UniqueViolationError, NotNullViolationError, CheckViolationError, ExclusionViolationError and RestrictViolationError keep their current behaviour, so #980's original motivation is preserved.

A deeper fix would serialize retains per document_id end to end. Operations carry a serialization_key, but ~40% of the backlog here predates it being populated, and the guard does not cover writers outside the retain queue.

Not a duplicate of

• #3227 — different constraint (fk_unit_entities_entity_id_entities), cause was deadlock rollback; that one was retried at task level
• #2662 — entity side, prune_orphan_entities
• #4251 — DeadlockDetectedError on the explicit document-delete endpoint