Relation search refresh cleanup exceeds asyncpg parameter limit (prod)

[phernandez]

Production evidence

Logfire issue #2589 recorded a pgq.job.resolve_relations failure in production on 2026-08-30 at 11:32:55 UTC. Trace: 01a05271a7f19527aad3e12f3621cd1a.

RelationRepository.clear_pending_search_refreshes() generated one DELETE ... id IN (...) statement for 44,926 refresh marker IDs. Including project_id, the statement exceeded asyncpg's hard limit of 32,767 query arguments and failed before retiring any completed refresh work.

The sibling complete_search_refresh_for_generation() method has the same unbounded IN expansion.

Required fix

• Bound both refresh-marker delete paths into multiple statements within the caller-owned transaction.
• Preserve project scoping and generation fencing.
• Add a regression test that proves the statement bound is load-bearing.

Verification

• Focused SQLite and PostgreSQL repository tests.
• Core static/type checks and full required PR CI.
• After release into Cloud, verify the production resolver completes without recurrence and then resolve Logfire #2589.

[claude]

Claude finished @phernandez's task in 45s —— View job

---

Triage

Type: Bug (confirmed by code inspection — production defect)
Priority: Critical — this fails the pgq.job.resolve_relations resolver outright in production (Logfire #2589), meaning completed relation-search-refresh work is never retired and the resolver cannot make progress once the pending-marker count crosses asyncpg's 32,767-argument limit.
Component: Cloud (Postgres/asyncpg-specific — the production job queue resolver, pgq.job.resolve_relations, running against Postgres). Also touches sync/core in prose: the affected code lives in RelationRepository.clear_pending_search_refreshes() and complete_search_refresh_for_generation() in src/basic_memory/repository/relation_repository.py (lines 673–684 and 686–714), called from src/basic_memory/indexing/relation_resolution.py.
Complexity: Medium — the fix is bounded to chunking the IN (...) delete into multiple statements inside the existing caller-owned transaction, but it must preserve project scoping and the generation-fencing predicate (current_relation_generation_predicate) exactly, since that fencing is the mechanism that prevents stale writers from clobbering a newer accepted generation. A regression test proving the chunk boundary is load-bearing (i.e., actually exercises >1 batch) is required per the issue.

I confirmed both methods build a single delete(...).where(...id.in_(refresh_ids)...) with no batching, so a large refresh_ids sequence (44,926 in the reported incident) will indeed exceed asyncpg's hard parameter cap once project_id (and generation predicate columns, for the second method) are added. No duplicate open issues found for this.

Labeled bug / cloud. Per triage scope, no fix will be implemented here — this is ready for someone to pick up.