PostgresToGCSOperator server-side cursor ignores cursor_itersize with psycopg3 driver (FETCH FORWARD 1)
- Dominant language
- Python
- Stars
- 46.9k
- Forks
- 17.8k
- Avg merge
- 2d 9h
- Merged PRs (30d)
- 472
Description
### Description
When using `PostgresToGCSOperator` with `use_server_side_cursor=True` and `cursor_itersize=10000`, the operator issues `FETCH FORWARD 1` per row instead of batching when the psycopg3 driver is active (providers-postgres >= 7.0.0).
The `_PostgresServerSideCursorDecorator` uses `fetchone()` for the psycopg3 code path, which bypasses the cursor's `itersize` attribute entirely. With psycopg2 it iterated over the cursor (which respects `itersize`).
### Impact
A 2M row table went from ~5 min to 90+ min (17x slower). The operator becomes unusable for large tables with the psycopg3 driver.
### Steps to Reproduce
1. Use `apache-airflow-providers-postgres>=7.0.0` (psycopg3 default)
2. Configure `PostgresToGCSOperator` with `use_server_side_cursor=True, cursor_itersize=10000`
3. Run against a table with >100k rows
4. Observe `pg_stat_activity` shows `FETCH FORWARD 1` instead of `FETCH FORWARD 10000`
### Expected Behavior
Server-side cursor should fetch in batches of `cursor_itersize` (e.g. `FETCH FORWARD 10000`).
### Environment
- Airflow 3.3.1
- apache-airflow-providers-google 22.1.0
- apache-airflow-providers-postgres 7.0.0+ (bug present)
- Astro Runtime 3.3.5
### Workaround
Pin `apache-airflow-providers-postgres<7.0.0` to stay on psycopg2.
Contributor guide
Research direction
Locate the PostgresToGCSOperator and its _PostgresServerSideCursorDecorator, then compare the psycopg3 fetchone() path with the psycopg2 cursor iteration path. Reproduce with use_server_side_cursor=True and cursor_itersize=10000, and verify that psycopg3 fetches batches of that size rather than issuing FETCH FORWARD 1 per row.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- google-cloud, postgresql, python
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100