Task supervisor lingers indefinitely on CeleryExecutor when psutil.Process.wait() returns None (socket_cleanup_timeout never triggers)

### Under which category would you file this issue?

Task SDK

### Apache Airflow version

3.1.8

### What happened and how to reproduce it?

Task supervisor processes (`airflow worker -- `) remain alive indefinitely after their task subprocess has finished and exited. The `workers.socket_cleanup_timeout` safety-net (introduced in 3.0.2 via https://github.com/apache/airflow/pull/51180) never triggers because the supervisor never detects the child process exit — `_process_exit_monotonic` is never set.

On our production Celery workers running for ~3 days:
- **worker-0:** 26 stuck supervisors, 357 zombie processes, 0 running tasks
- **worker-1:** 30 stuck supervisors, 417 zombie processes, 0 running tasks

Each stuck supervisor holds ~340 MB of RSS. With 26–30 orphaned supervisors, this accounts for ~8–10 GB of wasted memory per worker pod, causing OOM kills on containers with 6–8 Gi limits.

## How to reproduce

1. Deploy Airflow 3.1.8 with CeleryExecutor using `dumb-init` as container entrypoint (standard Helm chart setup):
```
command: ["/usr/bin/dumb-init", "--"]
args: ["bash", "-c", "exec airflow celery worker -H ${HOSTNAME}"]
```

2. Configure `worker_concurrency=16` (or any value)

3. Run a DAG with dynamically mapped tasks (using `expand()`), e.g. a task that produces many map instances

4. Wait for tasks to complete

5. Check for lingering supervisors:
```bash
# Inside the worker container:
count=0
for pid in /proc/[0-9]*; do
cmd=$(tr '\0' ' ' < "$pid/cmdline" 2>/dev/null)
case "$cmd" in "airflow worker -- "*) count=$((count+1));; esac
done
echo "live supervisors: $count"
```

6. Expected: 0 supervisors when no tasks are running

7. Actual: supervisors accumulate over time, never exit

### What you think should happen instead?

## Root cause analysis

### Environment
- **Airflow:** 3.1.8
- **Executor:** CeleryExecutor with `worker_concurrency=16`
- **Container init:** `dumb-init` (PID 1) — standard in Airflow Helm chart
- **Worker uptime:** ~2 days 18 hours
- **DAG triggering the issue:** `generate_compliance_report` with dynamically mapped task (`map_index=152`)

### Observed behavior and suspected bug chain

1. ForkPoolWorker-3 (PID 71) dispatches a mapped task to a new supervisor (PID 3183)
2. Supervisor creates 28 pipe pairs + 3 epoll instances for task communication
3. Supervisor forks a task subprocess to run the mapped task
4. Task subprocess finishes and exits
5. The child's exit status is not collected by the supervisor. psutil.Process.wait(timeout=0) returns None instead of an exit code, suggesting the exit status was consumed or lost before the supervisor could retrieve it.
6. `_process_exit_monotonic` is **never set**
7. The socket cleanup timeout check never evaluates to `True`:
```python
# From supervisor.py — this condition is never True because _process_exit_monotonic is None
if (
self._process_exit_monotonic
and time.monotonic() - self._process_exit_monotonic > SOCKET_CLEANUP_TIMEOUT
):
self._cleanup_open_sockets()
```
8. Supervisor sits in `epoll_wait()` forever with 28 dead pipe pairs
9. ~340 MB of memory is permanently wasted

### Evidence: stuck supervisor inspection (PID 3183)

**Process state — sleeping with no children:**
```
State: S (sleeping)
PPid: 71 ← parent is ForkPoolWorker-3
VmRSS: 338696 kB ← ~340 MB held by idle supervisor
Threads: 2
```

**No live or zombie children — exit status already consumed:**
```
Live children: (empty)
Zombie children (PPID=3183): (empty)
```

**96 open file descriptors — 28 pipe pairs to dead subprocess:**
```
3 anon_inode:[eventpoll] ← 3 epoll instances watching dead pipes
2 pipe:[4213839153] ← pipe pair to dead task subprocess
2 pipe:[4213839152]
2 pipe:[4213839151]
... (28 pipe pairs total)
1 /opt/airflow/logs/dag_id=generate_compliance_report/run_id=scheduled__2026-07-18T08:00:00+00:00/task_id=run_sql_for_client/map_index=152/attempt=1.log
```

**Syscall — blocked in recvfrom/epoll_wait:**
```
45 0x0 0x7f9aeb968d10 0x4 0x0 0x0 0x0 0x7ffcda5b8a70 0x7f9b1d184dfe
```

**socket_cleanup_timeout is configured but never fires:**
```
$ airflow config get-value workers socket_cleanup_timeout
60.0

$ kubectl logs ... --since=72h | grep "Process exited with open sockets"
(empty — zero matches in 72 hours)
```

### Evidence: worker-level impact

**kubectl top (no tasks running):**
```
POD NAME CPU(cores) MEMORY(bytes)
sandbox2-airflow-worker-0 airflow-worker 98m 5798Mi
sandbox2-airflow-worker-1 airflow-worker 3m 6487Mi
```

**Airflow DB confirms zero running tasks:**
```
No running tasks
```

**Process counts:**
```
worker-0: pool slots (ForkPoolWorkers): 16, stuck supervisors: 26, zombies: 357
worker-1: pool slots (ForkPoolWorkers): 16, stuck supervisors: 30, zombies: 417
```

**Top memory processes (all idle supervisors, no active tasks):**
```
347056 KB airflow worker -- 019f7c11-7ae5-7819-b15e-128d0d65eb87
346552 KB airflow worker -- 019f7c08-b0ee-791c-901b-4ea4e21371c3
346016 KB airflow worker -- 019f7c00-4d19-7e7e-8878-e82a35d3a8eb
345516 KB airflow worker -- 019f7c10-26bd-7013-b34c-551e753886b4
... (20+ more, all ~340 MB each)
```

### Operating System

RHEL 9.7 (container), Amazon Linux 2 (EKS node), kernel 5.10.236-228.935.amzn2.x86_64

### Deployment

None

### Apache Airflow Provider(s)

amazon, celery, http

### Versions of Apache Airflow Providers

apache-airflow-providers-amazon==9.31.0
apache-airflow-providers-celery==3.10.6
apache-airflow-providers-cncf-kubernetes==10.1.0
apache-airflow-providers-common-compat==1.15.0
apache-airflow-providers-common-io==1.7.3
apache-airflow-providers-common-sql==2.0.1
apache-airflow-providers-fab==3.7.0
apache-airflow-providers-http==6.0.4
apache-airflow-providers-postgres==6.0.0
apache-airflow-providers-smtp==3.0.1
apache-airflow-providers-snowflake==6.5.4
apache-airflow-providers-standard==1.15.0

### Official Helm Chart version

Not Applicable

### Kubernetes Version

v1.32.3-eks-473151a

### Helm Chart configuration

Using community chart: airflow-helm/charts v9.0.0
(https://github.com/airflow-helm/charts), not the official Apache Airflow Helm chart.

### Docker Image customizations

_No response_

### Anything else?

_No response_

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

Read supervisor.py around psutil.Process.wait(timeout=0), _process_exit_monotonic, and the socket_cleanup_timeout check. Reproduce with CeleryExecutor, dynamically mapped tasks, and the listed worker configuration, then verify that completed task supervisors exit and the cleanup-timeout evidence appears instead of supervisors accumulating with open pipes.

Written by the indexing model from the issue text.