GitHub user Ahsan221B added a comment to the discussion: Task starts time out 
on PUT /rtif after the 3.3.2 to 3.4.0 upgrade

+1 to @OswaldoRodriguez123's analysis. The unscoped v2 DELETE plus the 
unconditional v1 DELETE explains both wait-event patterns you captured. A few 
additions that may help with the issue and with preventing a repeat:

### 1. Abandoned requests keep running on the server

With `execution_api_timeout = 5` and 5 retries, the client gives up, but the 
API server most likely keeps executing the original request. A client 
disconnect doesn't cancel an in-flight SQL statement. That fits your numbers 
(126 task starts → 309 `PUT /rtif`, server-side durations up to 299 s): each 
retry added another transaction contending for the same rows while the earlier 
ones still held locks.

Until there's a fix, it's worth bounding this server-side, so a stuck cleanup 
fails fast instead of piling up. For example, set a role-level 
`statement_timeout` / `lock_timeout` for the Airflow DB user (PgBouncer passes 
these through if set on the role):

```sql
ALTER ROLE airflow SET lock_timeout = '5s';
ALTER ROLE airflow SET statement_timeout = '60s';
```

Test this carefully first, because `db migrate` and `db clean` may need longer 
limits. Run those with a separate role or a session-level override.

### 2. The `NOT IN (keep-list)` shape is O(history) even with scoping

Adding a `(dag_id, task_id)` predicate to the delete target is the main fix. 
But `run_id NOT IN (<N newest runs>)` still makes Postgres consider *every* 
historical run of that task on every task start, and that's 442k in your case. 
Two shapes that keep it proportional to the rows actually being evicted:

- **Cutoff instead of a keep-list:** look up the `run_after` of the Nth newest 
run once, then delete `WHERE dag_id = $1 AND task_id = $2 AND run_after < 
$cutoff`. This can use a range scan.
- **Batch it:** `... LIMIT 1000` per call (via a CTE / `ctid IN (SELECT ... 
LIMIT n)`), so one task start never pays for years of backlog.

### 3. Concurrent starts of the same task shouldn't serialize on the cleanup

Even after both fixes, N mapped instances or parallel runs of one task all try 
to delete the same rows. Wrapping the cleanup in 
`pg_try_advisory_xact_lock(hash(dag_id, task_id))` and skipping it when the 
lock is taken (or using `FOR UPDATE SKIP LOCKED` on the rows to delete) would 
stop concurrent starts of the same task from queueing behind each other. Even 
better: move the cleanup out of the `PUT /rtif` request path, for example into 
the scheduler or a periodic job. The upsert is the only part that needs to 
happen at task start.

### 4. Capturing the plan if it happens again

Since you couldn't capture the plan this time, enabling `auto_explain` on the 
DB during the next upgrade window would settle hypothesis 1 vs. 2 without 
needing a reproduction:

```sql
-- postgresql.conf / ALTER SYSTEM, then reload
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.l

GitHub link: 
https://github.com/apache/airflow/discussions/76774#discussioncomment-19601972

----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]

Reply via email to