Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets #51886

q121212 · 2024-06-20T15:39:26Z

q121212
Jun 20, 2024

Apache Airflow version

Other Airflow 2 version (please specify below)

If "Other Airflow 2 version" selected, which one?

2.9.1 and 2.9.2

What happened?

After migrating the PostgreSQL database to another server, we encountered an issue where the Airflow scheduler crashes with the following error:

 [2024-06-20T16:44:16.764+0300] {scheduler_job_runner.py:860} ERROR - Exception when executing SchedulerJob._run_scheduler_loop
 Traceback (most recent call last):
  File "/datadrive/env310_air/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/datadrive/env310_air/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
 psycopg2.errors.GroupingError: column "dataset.uri" must appear in the GROUP BY clause or be used in an aggregate function
 LINE 1: SELECT dataset.id, dataset.uri, dataset.extra, dataset.creat...
                           ^
 The above exception was the direct cause of the following exception:
 Traceback (most recent call last):
  File "/datadrive/env310_air/lib/python3.10/site-packages/airflow/jobs/scheduler_job_runner.py", line 843, in _execute
    self._run_scheduler_loop()
  File "/datadrive/env310_air/lib/python3.10/site-packages/airflow/jobs/scheduler_job_runner.py", line 989, in _run_scheduler_loop
    next_event = timers.run(blocking=False)
  File "/usr/local/lib/python3.10/sched.py", line 151, in run
    action(*argument, **kwargs)
  File "/datadrive/env310_air/lib/python3.10/site-packages/airflow/utils/event_scheduler.py", line 40, in repeat
    action(*args, **kwargs)
  File "/datadrive/env310_air/lib/python3.10/site-packages/airflow/utils/session.py", line 79, in wrapper
    return func(*args, session=session, **kwargs)
  File "/datadrive/env310_air/lib/python3.10/site-packages/airflow/jobs/scheduler_job_runner.py", line 1797, in _orphan_unreferenced_datasets
    orphaned_dataset_query = session.scalars(
  File "/datadrive/env310_air/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1755, in scalars
    return self.execute(
  File "/datadrive/env310_air/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1696, in execute
    result = conn._execute_20(statement, params or {}, execution_options)

Upon reviewing the call stack, it seems the crash occurs during the _orphan_unreferenced_datasets method. Here is its code:

@provide_session
def _orphan_unreferenced_datasets(self, session: Session = NEW_SESSION) -> None:
    """
    Detect orphaned datasets and set is_orphaned flag to True.

    An orphaned dataset is no longer referenced in any DAG schedule parameters or task outlets.
    """
    orphaned_dataset_query = session.scalars(
        select(DatasetModel)
        .join(
            DagScheduleDatasetReference,
            isouter=True,
        )
        .join(
            TaskOutletDatasetReference,
            isouter=True,
        )
        .group_by(DatasetModel.id)
        .having(
            and_(
                func.count(DagScheduleDatasetReference.dag_id) == 0,
                func.count(TaskOutletDatasetReference.dag_id) == 0,
            )
        )
    )

    updated_count = sum(self._set_orphaned(dataset) for dataset in orphaned_dataset_query)
    Stats.gauge("dataset.orphaned", updated_count)

It appears that the dataset, dag_schedule_dataset_reference, and task_outlet_dataset_reference tables are empty.

From my understanding, selecting all columns of a model without proper grouping might be incorrect.

My solution:

Correct, as far as I know the SQL syntax will only select DatasetModel.id

I really have a question - how did it work before the pg db migration? - the airflow code did not change (Maybe this function was simply not called for some reason before the database migration?). But in fact it looks like a bug.

What you think should happen instead?

The query in _orphan_unreferenced_datasets should handle cases where tables may be empty or ensure correct SQL grouping to avoid errors.

How to reproduce

Migrate PostgreSQL database to another server.
Ensure dataset, dag_schedule_dataset_reference, and task_outlet_dataset_reference tables are empty.
Run Airflow scheduler and observe the crash with the provided error.

Operating System

NAME="Red Hat Enterprise Linux" VERSION="8.9 (Ootpa)" ID="rhel" ID_LIKE="fedora" VERSION_ID="8.9" PLATFORM_ID="platform:el8" PRETTY_NAME="Red Hat Enterprise Linux 8.9 (Ootpa)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos" HOME_URL="https://www.redhat.com/" DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8" BUG_REPORT_URL="https://bugzilla.redhat.com/"  REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8" REDHAT_BUGZILLA_PRODUCT_VERSION=8.9 REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux" REDHAT_SUPPORT_PRODUCT_VERSION="8.9"

Versions of Apache Airflow Providers

apache-airflow-providers-common-io @ file:///datadrive/test/apache_airflow_providers_common_io-1.3.1-py3-none-any.whl
apache-airflow-providers-common-sql @ file:///datadrive/whls/other/apache_airflow_providers_common_sql-1.5.2-py3-none-any.whl
apache-airflow-providers-fab @ file:///datadrive/test/apache_airflow_providers_fab-1.1.0-py3-none-any.whl
apache-airflow-providers-ftp==2.0.1
apache-airflow-providers-http==2.0.2
apache-airflow-providers-imap==2.1.0
apache-airflow-providers-mysql @ file:///datadrive/whls/other3/apache_airflow_providers_mysql-5.3.0-py3-none-any.whl
apache-airflow-providers-postgres @ file:///datadrive/whls/other/apache_airflow_providers_postgres-5.5.1-py3-none-any.whl
apache-airflow-providers-smtp @ file:///datadrive/test/apache_airflow_providers_smtp-1.7.0-py3-none-any.whl
apache-airflow-providers-sqlite==2.0.1

Deployment

Other

Deployment details

deployed as linux service

Anything else?

No response

Are you willing to submit PR?

Yes I am willing to submit a PR!

Code of Conduct

I agree to follow this project's Code of Conduct

bufubaoni · 2025-06-18T13:20:56Z

bufubaoni
Jun 18, 2025

I think you might be encountering the same issue as I did, but I've identified the root cause. It turns out that incorrect indexes on the dataset table can lead to this query error. You can check the indexes on the dataset table by running the command \d+ dataset in the Airflow database.

I suspect that during the data migration, only the data was restored, and the table indexes were not correctly migrated, which led to this issue.

FYI @q121212

0 replies

potiuk · 2025-06-18T13:44:33Z

potiuk
Jun 18, 2025
Collaborator

Yep converting to a discussion. My few recommendations:

upgrade to latest airflow version (or even latest version in "line" of your development (I think here were four 2.9* with ONLY bugfixes so you are missing all the few tens of them that were implemented since 2.9.1 even if you do not want to upgrade to 2.10.5 or 2.11.0)
wipe-out and re-create your database

2 replies

q121212 Jun 18, 2025
Author

@bufubaoni
Thanks
I believe the your's explanation isn’t just valid—it’s the right way to solve it. Ensuring that dataset has all expected indexes is more robust, sustainable, and safe than a quick patch. It fixes the symptom and root cause, aligning our database to what Airflow actually expects.

bufubaoni Jun 18, 2025

@q121212
I believe there were some unexpected situations that led to data migration, but the corresponding indexes were not restored, including but not limited to the dataset_pkey index on the dataset table. In my actual maintenance experience, the database was reset, and only the table data was restored, but the indexes were not correctly restored.
This manifested as the scheduler being unable to start. The fix referenced this pull request.
However, I recently discovered that this SQL query can run correctly with the proper indexes in place:

SELECT 
    dataset.id, 
    dataset.uri,
    dataset.extra,
    dataset.created_at, 
    dataset.updated_at, 
    dataset.is_orphaned
FROM 
    dataset 
LEFT OUTER JOIN 
    dag_schedule_dataset_reference 
ON 
    dataset.id = dag_schedule_dataset_reference.dataset_id 
LEFT OUTER JOIN 
    task_outlet_dataset_reference 
ON 
    dataset.id = task_outlet_dataset_reference.dataset_id
WHERE 
    NOT dataset.is_orphaned 
GROUP BY 
    dataset.id
HAVING 
    COUNT(dag_schedule_dataset_reference.dag_id) = 0 
    AND COUNT(task_outlet_dataset_reference.dag_id) = 0;

But it fails to run correctly when the indexes are missing. This also explains why the code, which has never been modified, suddenly started having issues—it's such an edge case. It's almost certain that we encountered the same problem.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets #51886

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Replies: 2 comments 2 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Select a reply

Uh oh!

Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets #51886

Uh oh!

Uh oh!

q121212 Jun 20, 2024

Apache Airflow version

If "Other Airflow 2 version" selected, which one?

What happened?

My solution:

What you think should happen instead?

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else?

Are you willing to submit PR?

Code of Conduct

Replies: 2 comments · 2 replies

Uh oh!

Uh oh!

bufubaoni Jun 18, 2025

Uh oh!

potiuk Jun 18, 2025 Collaborator

Uh oh!

Uh oh!

q121212 Jun 18, 2025 Author

Uh oh!

Uh oh!

bufubaoni Jun 18, 2025

q121212
Jun 20, 2024

Replies: 2 comments 2 replies

bufubaoni
Jun 18, 2025

potiuk
Jun 18, 2025
Collaborator

q121212 Jun 18, 2025
Author