Analytics TimescaleDB lane fails intermittently on a concurrent continuous-aggregate refresh

[abdulrafey1]

Summary

The Analytics TimescaleDB Lane CI job fails intermittently with a LockNotAvailableError while resetting state between tests. The failure is timing-dependent and unrelated to the code under test — it lands on whichever PR happens to lose the race, and a re-run with no change goes green.

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error)
<class 'asyncpg.exceptions.LockNotAvailableError'>: could not refresh continuous
aggregate "login_activity_daily" due to a concurrent refresh
DETAIL:  A concurrent refresh on window [4714-11-24 00:00:00+00 BC, infinity) is already in progress.
[SQL: CALL refresh_continuous_aggregate(CAST($1 AS regclass), NULL, NULL)]
[parameters: ('login_activity_daily',)]

It surfaces as an ERROR during fixture setup, so the affected test never runs (2 passed, 1 error).

Cause

Two refreshes of the same continuous aggregate overlap inside a single CI job.

1. sparkth/migrations/analytics/versions/287f281e6558_create_login_activity_daily_cagg.py:52-57 registers a background refresh policy when the aggregate is created:

   SELECT add_continuous_aggregate_policy(
       'login_activity_daily',
       start_offset      => INTERVAL '3 days',
       end_offset        => INTERVAL '1 hour',
       schedule_interval => INTERVAL '1 hour'
   )

   TimescaleDB's background worker runs the policy's first pass shortly after registration, not an hour later. The pg fixtures apply these migrations to the target database, so the policy is registered on every CI run.

2. tests/analytics/pg/conftest.py:96-97 resets state before each test by truncating raw_events and then full-refreshing every aggregate via _refresh_all_caggs (conftest.py:47-51), which issues refresh_continuous_aggregate(..., NULL, NULL) in AUTOCOMMIT. A full refresh is required here because continuous aggregates cannot be isolated by transaction rollback — this is the documented reason the pg lane exists in the form it does.

When the background policy pass and the fixture's full refresh overlap, the latter cannot take the lock and raises. The window in the error message — [-infinity, infinity) — identifies it as the fixture's full refresh, colliding with the policy's trailing-window one.

Each CI job gets its own timescaledb service container (.github/workflows/ci.yml:75-93), so this is entirely self-inflicted within one run; concurrent PRs do not interfere with each other.

Evidence that it is timing, not code

• Six stacked PRs (chore(chat): drop the unused conversation.system_prompt column #579-docs: document the preferred language setting #583, refactor(api): assemble api_router in the v1 package __init__ #585) pushed together and ran at 2026-08-10T09:26. Five passed the lane; one failed — run 31374563982, job 93410778085 on refey/feat/lang-02-core-config, whose diff touches only .env, docs/reference/configuration.md, sparkth/core/config.py and tests/core/test_language.py — nothing analytics-related.
• The same lane failed on a different branch (refey/docs/lang-05-guide) at 07:24 and passed at 07:32.
• Re-running the failed job with no code change passed.

Why it matters

• Red builds on PRs that cannot have caused them, which trains reviewers to ignore this lane.
• The failure is an ERROR in setup, so test_pg_only_counts_login_events is silently not executed on affected runs — the lane reports failure, but had it been a flake in the other direction it would report success with reduced coverage.
• It affects every PR in the repo, since the lane runs on all non-draft PRs.

This description was written with the assistance of an LLM (Claude).

[abdulrafey1]

Proposed fix

The test lane does not need the background refresh policy — the fixtures refresh explicitly, before each test and on demand via pg_refresh. Removing the policy after migrating eliminates the only other writer, so nothing can contend for the lock.

In tests/analytics/pg/conftest.py, after the migrations are applied and before any fixture refreshes, drop the policy for every continuous aggregate:

SELECT remove_continuous_aggregate_policy(view_name, if_exists => true)
FROM timescaledb_information.continuous_aggregates
WHERE view_schema = 'public'

It must run in AUTOCOMMIT, like _refresh_all_caggs already does. Discovering the aggregates from timescaledb_information.continuous_aggregates rather than naming login_activity_daily keeps it correct as more aggregates are added — the same reason _refresh_all_caggs iterates rather than hardcoding.

This removes the race rather than narrowing it, and it only affects the test database.

Alternative, if the policy must stay

Retry _refresh_all_caggs on LockNotAvailableError with a short backoff. This is strictly weaker — it makes the race less likely to be observed rather than impossible, and it adds a retry loop to test infrastructure. Prefer removing the policy.

Points to verify during implementation

• The first policy pass may already be running when the fixture starts, so remove_continuous_aggregate_policy alone may not be sufficient on the very first test if a pass is mid-flight. Confirm whether removal waits for or cancels an in-flight job; if it does not, the setup may also need to wait for timescaledb_information.jobs / job_stats to report no running job before the first refresh.
• view_schema = 'public' matches how _refresh_all_caggs currently discovers aggregates — confirm that is still the schema in use rather than assuming it.
• Because the failure is timing-dependent, a single green run does not prove the fix. Verify by running the lane repeatedly (or by artificially forcing a policy pass immediately before a fixture refresh and confirming it no longer raises).

Reproducing locally

make test.backend.analytics starts the backing services and runs the lane. The race needs the policy's first pass to coincide with a fixture refresh, so it may not reproduce on demand — forcing a policy run immediately before _refresh_all_caggs is the reliable way to observe it.

This proposed fix was written with the assistance of an LLM (Claude).