[Bug] Workspace watch under PostgreSQL full-reindexes every change and leaks Postgres connections

[T012m3n7oR]

Describe the Bug

Two tightly coupled defects show up together under REPOWISE_DB_URL (shared PostgreSQL) with repowise watch --workspace:

1. Connection leak / pool exhaustion (symptom that surfaces first)

Long-running watch (alongside serve / mcp on the same DB) eventually fails with:

asyncpg.exceptions.TooManyConnectionsError:
remaining connection slots are reserved for roles with the SUPERUSER attribute

That is a server-side exhaustion of non-superuser slots (max_connections − superuser_reserved_connections). It is not a client QueuePool timeout.

Why connections accumulate

Postgres engines are created via create_engine() with no poolclass / pool_size override:

# packages/core/src/repowise/core/persistence/database.py
else:
    # PostgreSQL — asyncpg handles its own connection pool   # misleading
    kwargs["pool_pre_ping"] = True

engine = create_async_engine(db_url, **kwargs)  # → AsyncAdaptedQueuePool defaults

SQLite short-lived engines correctly use NullPool. Postgres short-lived engines get SQLAlchemy’s default pool (pool_size=5, max_overflow=10 → up to 15 server connections per engine). asyncpg does not own a separate pool here; SQLAlchemy does.

Every workspace update opens one or more of these engines. If an engine is not dispose()d in the same event loop that created it — or is abandoned across asyncio.run() boundaries — pooled asyncpg connections stay open on Postgres until the backend times them out. That is a connection leak (client-side ownership lost; server-side slots remain occupied). The same lifecycle class is documented in #2062 for the cost-tracker path; watch/update creates many more engines under the gate below, so the leak is amplified and eventually hits TooManyConnectionsError.

2. Incremental updates never run (root amplifier)

Under Postgres, per-repo indexes live in the shared DB and no <repo>/.repowise/wiki.db is created. update_single_repo_index still requires that file before taking the incremental path:

# packages/core/src/repowise/core/workspace/update.py — update_single_repo_index
if (
    not config_changed
    and base_ref
    and (repo_path / ".repowise" / "wiki.db").is_file()  # always false on Postgres
    and commit_exists(repo_path, str(base_ref))
):
    incremental_result = await _incremental_repo_update(...)
# else → index_repo_full(...)

So every watch trigger / update_workspace pass does a full index_repo_full, which calls create_engine (and init_db) again. Concurrent members (up to _MAX_CONCURRENT_UPDATES) multiply that. Full re-index on every filesystem event is what makes the leak fatal in practice.

Same wiki.db-as-“already indexed” assumption as #1034 / #2138 / #2139 / #2140, but this call site is the write/update path watch uses — fixing serve/list/search alone does not stop full re-indexes or the connection growth.

Observed on v0.48.0 (fbb78c4); gate still present in v0.49.0.

Steps to Reproduce

A — Wrong update path (deterministic)

1. Workspace with ≥1 repo; REPOWISE_DB_URL=postgresql+asyncpg://….
2. Index so Postgres has repositories (+ graph) rows; confirm no <repo>/.repowise/wiki.db.
3. Ensure state.json has last_sync_commit equal to HEAD.
4. Run with logging that shows pipeline choice, or set a breakpoint / temporary log on the wiki.db.is_file() branch.
5. repowise watch --workspace, then touch one tracked source file.

Expect: incremental update. Actual: index_repo_full.

B — Connection leak under watch (needs a few cycles)

1. Same Postgres (SHOW max_connections; — e.g. 100).
2. Start serve, mcp, and repowise watch --workspace against that DB.
3. In another session, sample connections every few seconds while triggering updates (edit files or FF merges):

-- run repeatedly during watch activity
SELECT
  count(*) AS total,
  state,
  application_name,
  left(query, 60) AS query_preview
FROM pg_stat_activity
WHERE datname = current_database()
  AND usename = current_user   -- app role, not superuser
GROUP BY 2, 3, 4
ORDER BY total DESC;

SELECT count(*) AS app_backends
FROM pg_stat_activity
WHERE datname = current_database()
  AND usename = current_user;

SHOW max_connections;
SHOW superuser_reserved_connections;

4. Continue until watch logs TooManyConnectionsError, or until app_backends climbs toward max_connections - superuser_reserved_connections and does not return to the idle baseline after each update finishes.

Leak signature: after a watch cycle completes, non-superuser backend count stays elevated (idle/idle in transaction rows accumulate) instead of returning to ~serve+mcp steady state. Peak-only spikes that always drain are exhaustion under load; a rising floor between cycles is a leak.

Expected Behavior

• Already-indexed workspace members on a shared Postgres URL take the incremental path when last_sync_commit is valid — wiki.db must not be required.
• Every create_engine() for a short-lived update must dispose() in the same event loop before that loop closes; Postgres should use NullPool or an explicit tiny pool so abandoned engines cannot hold a dozen server slots each.
• After an update finishes, Postgres backends for the app role should return to the long-lived serve/mcp baseline.

Actual Behavior

• Incremental gate fails → full re-index every watch event.
• Short-lived Postgres engines use default QueuePool; undisposed / cross-loop engines leak server connections.
• Watch eventually raises TooManyConnectionsError.

Environment

• OS: Linux (Kubernetes / container)
• Python: 3.12 (image runtime)
• Repowise: 0.48.0 (gate verified in 0.49.0 tree)
• Install: Docker, REPOWISE_DB_URL=postgresql+asyncpg://…, workspace mode
• Processes on one DB: repowise serve, repowise mcp, repowise watch --workspace
• Postgres: single-instance CNPG, max_connections=100

Additional Context

Related issues

Issue	Why related
#1034, #2138, #2139, #2140	Same incorrect wiki.db “indexed?” signal on other call sites
#2062	Postgres QueuePool + engine not disposed in the creating loop → leaked/unusable asyncpg connections

Call sites

Incremental gate — packages/core/src/repowise/core/workspace/update.py (update_single_repo_index). Docstring still says “plus an existing wiki.db”. Config-drift branch and reconcile_repo_head_commit in the same file also key off wiki.db.

Watch — packages/cli/src/repowise/cli/commands/watch_cmd.py → update_workspace(..., include_working_tree=True) → update_single_repo_index.

Pool — packages/core/src/repowise/core/persistence/database.py create_engine() Postgres branch (quoted above).

Full index engine — packages/core/src/repowise/core/pipeline/full_index.py does dispose() in finally when that path completes cleanly; leak still occurs when other engines on the update path are left open, when dispose races a closed loop (#2062 class), or when many pooled engines overlap across concurrent full re-indexes.

Diagnosis helpers

1. Confirm the gate without waiting for OOM / connection death — temporary log (or PYTHONBREAKPOINT) at the wiki.db check:

# in update_single_repo_index, immediately before the incremental `if`
_wiki = (repo_path / ".repowise" / "wiki.db").is_file()
_log.info(
    "workspace_update_path_choice",
    alias=alias,
    wiki_db_exists=_wiki,
    base_ref=base_ref,
    config_changed=config_changed,
    path="incremental" if (_wiki and base_ref and not config_changed) else "full",
)

On Postgres you should see wiki_db_exists=False / path=full even when the shared DB already has a full index.

2. Confirm leak vs transient spike — sample app_backends (SQL above) on a timer:

# example: every 5s for 10 minutes while watch is running
while true; do
  psql "$REPOWISE_DB_URL_PSYCOPG" -Atc \
    "SELECT now(), count(*) FROM pg_stat_activity
     WHERE datname = current_database() AND usename = current_user;"
  sleep 5
done

Plot or inspect: a rising baseline between update bursts = leak. A flat baseline with tall spikes only during index_repo_full = pool sizing / concurrency (still harmful; still caused by the full-reindex gate).

3. Attribute backends — if the app sets application_name (or you can add it on connect), group pg_stat_activity by application_name / client_addr to see whether growth is from the watch process vs serve/mcp.

Suggested fix

1. Gate: when get_configured_db_url() is Postgres, decide “already indexed” from shared DB / last_sync_commit (not wiki.db.is_file()). Keep wiki.db only for SQLite.
2. Pool / lifecycle: use NullPool (or pool_size=1, max_overflow=0) for short-lived Postgres engines; ensure every create_engine has a matching await engine.dispose() in the same loop (same discipline as [Bug] Async PostgreSQL cleanup races with event-loop shutdown #2062).
3. Until (1) lands, watch-on-Postgres will keep forcing full re-indexes and keep pressure (and leak surface) high.

[Aman-goel-04]

Thanks for the detailed writeup. I've spent significant time reproducing both defects locally and can offer solid findings for each.

Bug 1: Incremental gate (fully reproduced)

Setup: two disposable repos initialized against a Docker postgres:16 instance via REPOWISE_DB_URL=postgresql+asyncpg://.... Confirmed via direct query that Postgres held a repositories row for test-repo (commit 9958489...), state.json.last_sync_commit matched git rev-parse HEAD, and <repo>/.repowise/wiki.db did not exist.

Running repowise watch --workspace -v and touching a tracked file reliably produces:

test-repo: 1 changed file(s), updating...
...
Git indexing complete   duration=0.1s files=1 hotspots=0 stable=0 tier=full
...
pipeline_result_persisted

tier=full on every single-line change, repeatable across dozens of edits, matching Repro A exactly.

I also wrote a regression test (test_shared_db_indexed_repo_takes_incremental_path in tests/unit/workspace/test_incremental_update.py) using a file-backed sqlite+aiosqlite URL as a stand-in for "externally configured DB." It isolates the routing bug without requiring a live Postgres server, and fails with full pipeline must not run for indexed repos against the current code, confirming the routing bug independent of any Postgres-specific detail.

I have a working fix in local testing: a _has_persisted_repo_index() helper in update.py that checks get_configured_db_url(), deliberately not resolve_db_url(), since the latter falls back to local SQLite and reintroduces the ambiguity, and if a shared DB is configured, queries get_repository_by_path() against it instead of checking wiki.db.is_file(). Both the incremental-path condition and the config-drift branch now share a single has_persisted_index value, computed once up front before either branch reads it.

(That ordering detail matters: my first pass computed has_persisted_index between the two branches, so the config-drift branch referenced it before assignment. This was harmless when config_changed is False since Python short-circuits, but it was a live UnboundLocalError waiting to happen the moment a shared-DB repo's config actually drifted. I caught it on self-review before running it against anything real, fixed the ordering, and added test_shared_db_config_drift_does_not_crash, a regression test combining config_changed=True with the shared-DB/no-local-wiki.db case, which is the only combination that exercises the buggy line. Full test file now passes 17/17.)

I haven't touched _merged_repo_excludes()'s raw sqlite3.connect yet. That needs its own shared-DB decision and I'd rather tackle it deliberately as part of the same PR.

Bug 2: Connection leak (root-caused, not reproduced as an accumulating leak)

Confirmed by inspection: the Postgres branch of create_engine() sets no poolclass, so it gets AsyncAdaptedQueuePool defaults (pool_size=5, max_overflow=10) instead of NullPool like the SQLite branch.

However, across extensive testing I was not able to observe a rising floor, the leak signature the issue itself defines (as opposed to a draining spike, which is expected/benign). Specifically, with serve + mcp + watch all running against the same Postgres instance (max_connections=100, superuser_reserved_connections=3):

• ~30 sequential single-repo edit cycles (5s apart, each producing its own full create_engine -> init_db -> persist -> dispose cycle, confirmed via watch logs): connection count held at baseline (2) with only isolated spikes to 3 that always drained within one 5s sample.
• ~20 sequential dual-repo cycles: same result, no accumulation.
• ~15 rounds of forced concurrent dual-repo edits (both files written near-simultaneously via backgrounded shell jobs, to force genuine overlapping update_workspace cycles rather than serialized ones): still no accumulation. One sample did show a connection in idle in transaction state running a pg_type catalog-introspection query, consistent with a brand-new engine's one-time asyncpg type-codec setup, which is useful confirmation that concurrent engine creation was actually happening. It drained on the next sample.

Every measured spike across all three tests returned to baseline before the next sample. I also confirmed, before/between runs, that serve/mcp startup alone adds exactly one persistent idle connection (from serve), and mcp adds none at rest.

My interpretation: Bug 1 is likely the dominant real-world driver of connection pressure (many more create_engine() calls than necessary, one per full-reindex), but I couldn't trigger accumulation from watch's update cycles alone, even under forced concurrency. This suggests the leak may require actual request traffic against serve/mcp happening concurrently with watch (both of ours were up but idle, serving zero requests). I'd want to script simulated load against serve alongside a watch loop as the next step, and/or investigate whether an exception path in the update pipeline skips engine.dispose() (all my test edits succeeded cleanly, so I haven't exercised any error path). Happy to also dig further into #2062 for the cross-loop dispose pattern once assigned.

@RaghavChamadiya, I'd like to take this on. I have Bug 1's fix ready, and a clear next-steps plan for Bug 2's leak investigation (load-testing serve concurrently with watch, and fault-injection on the update pipeline's error paths). Could this be assigned to me?

[RaghavChamadiya]

Thanks @T012m3n7oR for the report and @Aman-goel-04 for the reproduction. Two things about this pair are unusually good and worth naming: the report defines the leak signature up front (a rising floor between cycles, not a draining spike), which is what made it falsifiable, and the reproduction then went and falsified half of it rather than confirming what it set out to confirm. That is worth more than a fix.

#2148 is yours, @Aman-goel-04. Assigned.

Everything in the report checks against current main. The gate is update.py:588-592, the config-drift branch keys off the same file at :580, the docstring at :556-557 still says "plus an existing wiki.db", and the Postgres branch of create_engine sets only pool_pre_ping (persistence/database.py:267-269) while the SQLite branch takes NullPool at :266.

Start with the comment on that Postgres branch. "asyncpg handles its own connection pool" is false as written, and it is the reason this went unexamined: asyncpg owns a pool only when something calls asyncpg.create_pool, and nothing here does. SQLAlchemy pools these connections with AsyncAdaptedQueuePool, so the report's 5 + 10 arithmetic is right. Fix the comment in the same PR as the pool, or the next person re-derives all of this.

Two more call sites in the same file, both with consequences nobody has reported yet

reconcile_repo_head_commit returns before doing anything when there is no local wiki.db (update.py:316). Read its own docstring for what that costs: it is the function that stamps repositories.head_commit and advances updated_at, and the server reads the indexed commit for /api/repos and the MCP _meta from that row, not from state.json. So on a Postgres workspace that row is never stamped after an update, and the "index behind checkout" signal is stuck on, permanently, for every member. The freshness timestamp in the health overview is frozen at the last full index for the same reason. That is a visible-in-the-UI bug that no one has filed, and it is three lines from the gate you are already fixing.

It also confirms the call you made on resolve_db_url: that function goes on to use resolve_db_url(repo_path) at :328, which is exactly the fallback-to-local-SQLite ambiguity you avoided by reaching for get_configured_db_url() instead. Same treatment needed here.

_merged_repo_excludes (update.py:60-71) opens wiki.db with raw sqlite3.connect to read settings_json.exclude_patterns, and silently returns only the config.yaml patterns when the file is absent. On Postgres that means every workspace update runs with a smaller exclude set than the repo was indexed with, so paths the user excluded through settings come back. You said you would rather take it deliberately in the same PR, and I agree: it needs the shared-DB read, not a guard.

That makes four sites, and I would rather they moved together than in four passes over one file.

On bug 2, your refutation is the more useful result

Do not treat "I could not reproduce an accumulating leak" as a gap in the work. It is the finding, and the code supports it: full_index.py:53-65 wraps its engine in try / finally: await engine.dispose(), so both the success path and any exception raised inside persistence dispose correctly. Given that, a rising floor from watch cycles alone would have been surprising, and your ~65 cycles across three shapes are enough for me to say the report's leak framing does not hold for this path as written.

What survives is narrower and still worth fixing: a short-lived engine that reaches Postgres with default pool settings can hold up to 15 server slots for as long as it lives, and the only thing standing between that and exhaustion is that every creation site disposes correctly, in the right loop. That is a discipline you cannot enforce by review across a codebase this size, and #2062 was the proof: an engine that was disposed, from the wrong loop, on a path nobody thought about. NullPool (or pool_size=1, max_overflow=0) for short-lived Postgres engines makes the entire class unreachable rather than merely absent today.

So: do not block your PR on reproducing an accumulating leak. Ship the gate plus the pool default plus the comment, and say in the PR body what you measured and did not see, so the next person does not go hunting for a leak the evidence says is not there. If you still want the load-and-fault-injection experiment afterwards, I would rather have it as its own issue with your numbers in it than as an open question hanging off this one.

One correction to your plan while you are here: #2062 landed earlier today (#2083 merged), and the fix was to dispose eagerly inside make_cost_tracker's own loop rather than to arrange a tidier late dispose. Worth reading that diff before the fault-injection work, since it is the shape of the answer for the cross-loop class generally.

Labelling bug.

[Aman-goel-04]

@RaghavChamadiya, Thanks for the detailed scope, that's exactly what I needed. Opened #2160 covering all four call sites plus the create_engine pool fix and comment. Also read the #2083 diff before starting, matches what you described (dispose eagerly inside the creating loop, not a tidier late dispose) and I followed the same pattern for the two long-lived engines that needed to opt out.

PR description has the full test plan, including the honest "did not reproduce an accumulating leak" writeup with the ~65-cycle numbers, per your note not to block on chasing that further.

Please review when you get the time!!

[Aman-goel-04]

@swati510 @RaghavChamadiya, Please review this one too when y'all get the time!