Increasing 'max_map_length' leads to SQL 'max_stack_depth' error with 5000 dags to be spawned #28478

chest3x · 2022-12-19T21:02:44Z

chest3x
Dec 19, 2022

Hello,

First of all, thanks for this wonderful project.

There is one issue I am facing lately.
I am using TriggerDagRunOperator.partial(...).expand(...) to spawn multiple processing dags.

While this works well while having less than 1024 dags to be spawned, with higher number, which is approx. 5000 I am getting following error message from the PostgreSQL: sqlalchemy.exc.OperationalError: (psycopg2.errors.StatementTooComplex) stack depth limit exceeded which is received during dag_run.schedule_tis updating the DB state to SCHEDULED.

Increasing the stack depth does not seem like a good idea, rather it feels like I should prevent all TIS to be updated at the same time.

Will putting a limit on max_active_tis_per_dag resolve this issue? If not, is there any other way to tackle this if I want to execute lets say 10000 dags over time?

Thanks,
Filip

hucKOder · 2022-12-20T14:22:03Z

hucKOder
Dec 20, 2022

Hey,

I came across similar use case, during my work.

Few Steps how to reproduce:

Latest Airflow chart
Airflow version == 2.4.0
Allowed the Postgres deployment from subchart - Postgres version - 11.12.0-debian-10-r44

Code for reference:

def build_trigger_dags(dag_: DAG, dependency_task: BaseOperator) -> MappedOperator:
    dag_run = int(Variable.get('MAX_DAGS', DEFAULT_MAX_DAGS))
    task = TriggerDagRunOperator.partial(
        task_id="trigger-dag",
        trigger_dag_id=DAG_ID,
        trigger_run_id=f"{dag_id_template}_{datetime.now().isoformat()}",
        max_active_tis_per_dag=max_dags,
        wait_for_completion=True,
        poke_interval=10,
        dag=dag_
    ).expand(conf=XComArg(dependency_task))

    return dag_run

Behaviour:
When the depedency_task outputs a list with more than 1024 records an Exception is raised for max_map_length.
If I tried to increased the max_map_length I received sqlalchemy.exc.OperationalError: (psycopg2.errors.StatementTooComplex) stack depth limit exceeded same as in this question.

My assumtions:
Based on this thread https://github.com/bitnami/bitnami-docker-postgresql/issues/334https://github.com/bitnami/bitnami-docker-postgresql/issues/334 I guess the newer version of postgres should support bigger stack_depth_limit. Anyone could confirm on this ?

I have tested max_active_tis_per_dag, it is not resolving this use case. I guess the stack_depth_limit is not a solution to this, if the dependency_task output is too big same thing will happen. I think under the hood Airflow scheduler job is trying to update state to SCHEDULED to all of the tasks in the DB at the same time.
Anybody knows any workaround for this ?

An expand of expand might work for this to limit the heavy Update query to DB.
Something like (Pseudocode):
dependency_task -> TriggerDagRunOperator.partial().expand() (Contains only one task to split 10k list to list of batches of 1024 max == 10 dags triggered of 1k batches) -> TriggerDagRunOperator.partial().expand() (Triggering 1024 dags at max from the passed batch)

Is my understanding correct ? Any suggestions on the pseudocode approach?

Thanks!

4 replies

hucKOder Dec 22, 2022

Just as a resolution for this thread.

Expand of expands (batching of scheduling of dags) worked for me, so basically if the default postgres from helm chart is deployed it can schedule 1024 dags at a time with expand() and when the max_active_runs is set to 1, one subdag will run at a time and can schedule 1024 subdags at a time.

I guess with newer Postgres version like 13,14,15 you could achieve bigger numbers by increasing max_map_length on Airflow side and stack_depth_limit on Postgres side.

Request for a feature
I would like to see the batching of scheduling with expand() to be a built in feature.
Basically if I pass a list of size 1 million into TriggerDagRunOperator.partial().expand() I would like Airflow scheduler to not execute a single complex update query with 1 million params but multiple "batched like" update queries with reasonable amount of parameters.

potiuk Dec 22, 2022
Collaborator

Good to hear. Can you please create a feature request for that, please?

mksafavi Jan 26, 2023

Is there any update on batched expand() scheduling?
Increasing stack_depth_limit on postgres 13 makes it possible for the UPDATE query to execute,
but airflow-scheduler becomes unresponsive after that. I'm currently running version 2.5.0

also, I couldn't find the feature request regarding this issue. I thought I should ask before making a duplicate.

potiuk Feb 19, 2023
Collaborator

If you can't find it, probably there is no issue. You can open an feature for that, that sounds like a good thing. Generally speaking - issues here are public, so if you can't find, probably there is not one

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Increasing 'max_map_length' leads to SQL 'max_stack_depth' error with 5000 dags to be spawned #28478

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Replies: 1 comment 4 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!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

Increasing 'max_map_length' leads to SQL 'max_stack_depth' error with 5000 dags to be spawned #28478

Uh oh!

Uh oh!

chest3x Dec 19, 2022

Replies: 1 comment · 4 replies

Uh oh!

Uh oh!

hucKOder Dec 20, 2022

Uh oh!

hucKOder Dec 22, 2022

Uh oh!

potiuk Dec 22, 2022 Collaborator

Uh oh!

mksafavi Jan 26, 2023

Uh oh!

potiuk Feb 19, 2023 Collaborator

chest3x
Dec 19, 2022

Replies: 1 comment 4 replies

hucKOder
Dec 20, 2022

potiuk Dec 22, 2022
Collaborator

potiuk Feb 19, 2023
Collaborator