You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "q121212 (via GitHub)" <gi...@apache.org> on 2025/06/18 13:42:37 UTC
[D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
GitHub user q121212 created a discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
### 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
1. Migrate PostgreSQL database to another server.
2. Ensure dataset, dag_schedule_dataset_reference, and task_outlet_dataset_reference tables are empty.
3. 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?
- [X] 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)
GitHub link: https://github.com/apache/airflow/discussions/51886
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni added a comment to the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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](https://github.com/apache/airflow/pull/40351).
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.
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13511037
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "q121212 (via GitHub)" <gi...@apache.org>.
GitHub user q121212 edited a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
@bufubaoni 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.
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13510613
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
GitHub user potiuk added a comment to the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509616
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "q121212 (via GitHub)" <gi...@apache.org>.
GitHub user q121212 edited a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
@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.
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13510613
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "q121212 (via GitHub)" <gi...@apache.org>.
GitHub user q121212 added a comment to the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
@bufubaoni 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 your database to what Airflow actually expects.
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13510613
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni edited a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509643
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni edited a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509587
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni edited a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
@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](https://github.com/apache/airflow/pull/40351).
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.
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13511037
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni added a comment to the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509643
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni deleted a comment on the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509643
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets [airflow]
Posted by "bufubaoni (via GitHub)" <gi...@apache.org>.
GitHub user bufubaoni added a comment to the discussion: Airflow scheduler crashes with a psycopg2.errors.GroupingError due to an incorrect SQL query when detecting orphaned datasets
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
GitHub link: https://github.com/apache/airflow/discussions/51886#discussioncomment-13509587
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org