Tasks are stuck in running state. "select 1" query stuck in idle in transaction

[deepak4babu]

trafficstars

Apache Airflow version

Other Airflow 2 version (please specify below)

What happened

Airflow version: 2.6.2, 2.7.1 Postgresql Version: 13.11

In our airflow setup, tasks are getting stuck in running state after this log line.

On further checking, we found that the "select 1" query from airflow is getting stuck in idle in transaction state. Once we kill the query manually, the task completes.

Below is our airflow configuration

AIRFLOW_USER_HOME_DIR=/home/airflow AIRFLOW__API__AUTH_BACKENDS=airflow.api.auth.backend.basic_auth,airflow.api.auth.backend.session AIRFLOW__CELERY__BROKER_URL=amqp://******** AIRFLOW__CELERY__RESULT_BACKEND=db+postgresql://*********** AIRFLOW__CORE__DAGS_ARE_PAUSED_AT_CREATION=False AIRFLOW__CORE__DAGS_FOLDER=/opt/airflow/dags/ AIRFLOW__CORE__DAG_IGNORE_FILE_SYNTAX=glob AIRFLOW__CORE__EXECUTOR=CeleryExecutor AIRFLOW__CORE__LOAD_EXAMPLES=False AIRFLOW__CORE__MAX_ACTIVE_RUNS_PER_DAG=1 AIRFLOW__CORE__MAX_ACTIVE_TASKS_PER_DAG=1024 AIRFLOW__CORE__MIN_SERIALIZED_DAG_FETCH_INTERVAL=10 AIRFLOW__CORE__MIN_SERIALIZED_DAG_UPDATE_INTERVAL=10 AIRFLOW__DATABASE__LOAD_DEFAULT_CONNECTIONS=False AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://******* AIRFLOW__DATABASE__SQL_ALCHEMY_POOL_PRE_PING=True AIRFLOW__LOGGING__BASE_LOG_FOLDER=/opt/airflow/logs AIRFLOW__LOGGING__LOGGING_LEVEL=INFO AIRFLOW__LOGGING__WORKER_LOG_SERVER_PORT=8793 AIRFLOW__METRICS__STATSD_HOST=*********** AIRFLOW__METRICS__STATSD_ON=True AIRFLOW__METRICS__STATSD_PORT=9125 AIRFLOW__METRICS__STATSD_PREFIX=airflow AIRFLOW__SCHEDULER__DAG_DIR_LIST_INTERVAL=2592000 AIRFLOW__SCHEDULER__ENABLE_HEALTH_CHECK=True AIRFLOW__SCHEDULER__MIN_FILE_PROCESS_INTERVAL=2592000 AIRFLOW__SCHEDULER__TASK_QUEUED_TIMEOUT=1800 AIRFLOW__SCHEDULER__TASK_QUEUED_TIMEOUT_CHECK_INTERVAL=600 AIRFLOW__WEBSERVER__BASE_URL=http://localhost:8080 AIRFLOW__WEBSERVER__SECRET_KEY=******** AIRFLOW__WEBSERVER__SHOW_TRIGGER_FORM_IF_NO_PARAMS=True AIRFLOW__WEBSERVER__WEB_SERVER_PORT=8082

What you think should happen instead

Task should complete without any stuck queries

How to reproduce

It happens randomly, we cannot reproduce it.

Operating System

NAME="Debian GNU/Linux" VERSION_ID="11" VERSION="11 (bullseye)" VERSION_CODENAME=bullseye ID=debian

Versions of Apache Airflow Providers

apache-airflow-providers-celery==3.3.3 apache-airflow-providers-common-sql==1.7.1 apache-airflow-providers-ftp==3.5.1 apache-airflow-providers-http==4.5.1 apache-airflow-providers-imap==3.3.1 apache-airflow-providers-sqlite==3.4.3

Deployment

Other

Deployment details

We have modified version of the official helm chart and airflow image to add some custom things. The core helm templates are all same.

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

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[Taragolis]

@deepak4babu Sorry for the late response. Is this problem still actual (maybe it resolve into the new version of Airflow). If it still relevant could you provide minimum reproducible example? It might help contributors or someone who want to help to reproduce and potentially fix it.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 14 days with no response from the author. It will be closed in next 7 days if no further activity occurs from the issue author.

[github-actions[bot]]

This issue has been closed because it has not received response from the issue author.