seanghaeli opened a new issue, #76959:
URL: https://github.com/apache/airflow/issues/76959
`PermittedTriggerFilter.to_orm`
(`airflow-core/src/airflow/api_fastapi/core_api/security.py`, from #76260)
narrows trigger queries in SQL with `Trigger.dag_id IN (readable Dags)` for
task triggers and again for callback triggers, plus `IN (readable assets)` for
watchers. So every `GET /api/v2/triggers` query, both the count and the page,
carries about two bind parameters per readable Dag, and `GET
/api/v2/triggers/{trigger_id}` runs the same filter to read one row. The
Triggers page, the trigger page and the dashboard card call these on every
auto-refresh.
The cost grows with the size of the deployment, not with the number of
triggers. For a caller who can read every Dag, on Postgres with the simple auth
manager, the list request took about 200 ms at 2,000 Dags and 1.25 s at 10,000
(about 50,000 bind parameters). At 50,000 Dags it fails with a 500, because
psycopg 3 refuses a statement with more than 65,535 parameters (`number of
parameters must be between 0 and 65535`). A caller who reads 25,000 of the
50,000 Dags hits the same limit.
Suggestion: keep the other filters and the sort in SQL, read only the
matching triggers' ids and what each is used by, check the readable Dags and
assets in Python, then load only the requested page by id. For the single
trigger, load the row by id and check it in Python. That keeps the totals and
ordering exact and the parameter count fixed: in the same setup the list took
70 ms at 2,000 Dags and 470 ms at 10,000, and both routes answered at 50,000.
- [ ] `GET /triggers`: check readable Dags and assets in Python, page by id,
with a test that totals and order match the SQL filter
- [ ] `GET /triggers/{trigger_id}`: load by id and check permission in Python
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
