Spike: Investigate Celery task hang and re-queue loop on large CSV submissions

[pooleycodes]

Background

When a submitted CSV file contains a large number of trailing empty rows (e.g. thousands of blank comma-delimited rows from an Excel export), the _save_response_details function attempts to write every row to the database. This causes the Celery task to hang for an extended period before the hard time limit (30 min) kills the worker process.

We can add a guard to skip saving empty rows at the DB write step, but there is a ‼️ deeper question about why the task appears to keep consuming compute after being killed.

Hypothesis

The following loop may be occurring in production:

1. Task starts, SQS message visibility timer begins (35 min)
2. Task hangs on session.commit() writing thousands of rows — this is a C-level psycopg2 call, so SIGALRM-based time limits may not interrupt it cleanly
3. If the hard kill does fire after 30 min, task_reject_on_worker_lost = True causes the parent worker process to NACK the message
4. SQS has no true NACK — it lets the visibility timeout expire, then re-delivers the message to another worker
5. The cycle repeats indefinitely

Goals of the spike

• Confirm whether the SQS queue has a dead letter queue (DLQ) configured — if not, re-delivery loops are unbounded
• Confirm whether task_time_limit is actually firing by checking CloudWatch worker logs for TimeLimitExceeded / WorkerLostError / Hard time limit exceeded
• Check ApproximateNumberOfMessagesNotVisible on the SQS queue in CloudWatch during a stuck task to determine if the same message is being re-processed
• Determine whether session.commit() blocking in psycopg2 is preventing SIGALRM delivery — consider adding a PostgreSQL statement_timeout on the DB session as a secondary kill switch
• Assess whether task_reject_on_worker_lost should be False for this use case (drop the message on hard kill rather than re-queue it), or whether a DLQ with a low maxReceiveCount is the right safeguard

Out of scope

The empty row fix at the DB write layer is already merged. This spike is about the task lifecycle and infrastructure behaviour, not the data quality problem.

Ways to research

• Generate a test CSV with over a million trailing empty rows to reliably reproduce the hang locally and in staging:

with open("big_empty.csv", "w") as f:
    f.write("reference,plan,plan-event,entry-date,event-date,actual-date,notes\n")
    f.write("test-ref,test-plan,publish-notice,01/01/2026,01/01/2026,,A real row\n")
    f.write(("," * 6 + "\n") * 1_000_000)

• Submit this file through the local stack and observe whether the task is killed cleanly, how long it takes, and whether it re-appears in the queue
• Run the worker with debug logging (--loglevel=debug) locally to see time limit signal events in the terminal
• In staging/production, query CloudWatch logs for the worker log group filtering on TimeLimitExceeded, WorkerLostError, SIGKILL, Hard time limit
  Monitor ApproximateNumberOfMessagesNotVisible and ApproximateNumberOfMessagesVisible on the SQS queue in CloudWatch during a stuck submission to confirm re-delivery looping

Definition of done

A short write-up of findings and a recommended approach (DLQ config, statement timeout, reject-on-lost behaviour, or combination).

[pooleycodes]

Broken this task down into more specific jobs to do, with #128 now taking priority.