apache / apache/airflow

PostgresToGCSOperator server-side cursor ignores cursor_itersize with psycopg3 driver (FETCH FORWARD 1)

Open
#72,075 1 comment 0 reactions 0 assignees View on GitHub
area:providers kind:bug provider:postgres
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.