Chunk drops deadlock (40P01) with the alert pass's database-state seed; the victim side has no retry #4606

Claude posting for Erik Darling.

What happened

A deadlock (SQLSTATE 40P01) between a chunk drop and a concurrent statement has hit three monitoring stores over 14 days, one each:

• 2026-09-17 15:56Z and 2026-09-23 15:54Z, two stores: a TimescaleDB policy_retention job on collection_log failed with "deadlock detected. Process … waits for AccessExclusiveLock on relation …" (timescaledb_information.job_errors, one row each). The job retries on its own schedule, so each cost one retention run.
• 2026-09-28 11:57:34Z, a third store: the deadlock victim was the alert pass's INSERT INTO config.database_state_expected … SELECT … FROM database_states … (AccessShareLock), against a SELECT drop_chunks(…) holding or waiting for AccessExclusiveLock. That cost one alert pass's database-state seed.

Each one is rare and self-heals, but the victim side has no handling, so the failure surfaces as an alert-pass error.

What the code does today (dev 93b3a278d)

• The product's own daily purge retries drop_chunks once on 40P01, then falls back to a batched DELETE (ExecuteDropChunksWithDeadlockRetryAsync, Darling/PerformanceMonitor.Darling.Service/DarlingRetention.cs ~1285–1318, from Retention-purge e2e can deadlock against the background jobs it just created (nightly flake; consider product-side 40P01 retry) #2143).
• The alert read adapter's database-state sequence has no 40P01 handling. That's the seed, heal and prune of config.database_state_expected in GetDatabaseStatesCoreAsync, Darling/PerformanceMonitor.Darling.Service/DarlingAlertReadAdapter.cs ~1446–1454 and ~1622–1676, each statement autocommit. Its ExecuteWithOneRetryAsync seam (~287–364) retries client-side command timeouts only; a PostgresException, 40P01 included, is excluded on purpose.
• Neither side sets lock_timeout.
• It's unconfirmed which code path created a TimescaleDB retention policy on collection_log on the two stores whose job failed. The product purges collection_log itself with drop_chunks.

Fix

1. Give the alert adapter's database-state sequence one retry on 40P01, in the same shape as the purge's retry. A deadlock victim's statement is rolled back whole, and the seed is ON CONFLICT DO NOTHING, so a retry is safe. Check the heal/prune statements are idempotent the same way, and log the retry once.
2. Confirm where the collection_log retention job comes from: grep RetentionPolicies, the historical rungs, and Lite's parity. If the product creates it, say so beside the purge. If not, note that an out-of-band policy on collection_log duplicates the product's own purge.
3. Pins:
   • the classifier/seam treats 40P01 as a one-shot retry;
   • a simulated deadlock (a fake PostgresException with 40P01, as DarlingRetentionTests' DropChunksRetry_* do) retries once and succeeds, and twice gives up and surfaces;
   • a non-40P01 failure doesn't retry.

Related: #2143, #1801.