bug(source-postgres): Resumable full refresh generates invalid SQL with null cursor field
- 主要言語
- Python
- スター
- 22.1k
- フォーク
- 5.4k
- 平均マージ
- 5時間
- マージ済み PR(30日)
- 671
説明
## Summary
When syncing streams configured as `syncMode=full_refresh` with an empty `cursorField=[]`, the Postgres source connector's resumable full refresh code path generates invalid SQL that causes the error: `ERROR: column "null" does not exist`.
## Connection Details
- Connection URL: https://cloud.airbyte.com/workspaces/4835be10-184d-4b06-8cc1-730e4d6b76a0/connections/1cd35c07-9e42-4490-9c77-0f9bcff9db4b/timeline
- Affected streams: `airtravel.user_searches`, `airtravel.tracked_flight_routes`, `airtravel.flight_search_request`
## Error Message
```
java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: column "null" does not exist
Position: 10
at io.airbyte.integrations.source.postgres.PostgresQueryUtils.lambda$getCursorBasedSyncStatusForStreams$4(PostgresQueryUtils.java:192)
```
## Root Cause Analysis
Show/Hide Detailed Report
### Configuration
The failing streams are configured as:
```
syncMode=full_refresh, cursorField=[], destinationSyncMode=overwrite
```
The logs show these streams are being processed through the resumable full refresh path:
```
will sync in resumeable full refresh mode
```
### Code Path
1. In `CursorManager.kt` (lines 183-189), when the catalog has a `cursorField` list that is present but empty, the code extracts `null`:
```kotlin
cursorField =
streamOptional
.map { obj: ConfiguredAirbyteStream -> obj.cursorField }
.flatMap { f: List ->
if (f.size > 0) Optional.of(f[0]) else Optional.empty()
}
.orElse(null)
```
2. This results in the log message:
```
No cursor field set in catalog but not present in state. Stream: airtravel_user_searches, New Cursor Field: null. Resetting cursor value
```
3. In `PostgresSource.getIncrementalIterators` (line 636-637), `getCursorBasedSyncStatusForStreams` is called with these streams.
4. In `PostgresQueryUtils.getCursorBasedSyncStatusForStreams` (lines 166-175), the code builds a SQL query using the null cursor field:
```java
final String cursorField = cursorInfoOptional.get().getCursorField();
final String cursorBasedSyncStatusQuery = String.format(MAX_CURSOR_VALUE_QUERY,
cursorField, // This is null!
fullTableName,
cursorField,
cursorField,
fullTableName);
```
5. With `MAX_CURSOR_VALUE_QUERY = SELECT "%s" FROM %s WHERE "%s" = (SELECT MAX("%s") FROM %s);`, when `cursorField` is `null`, the query becomes:
```sql
SELECT "null" FROM "airtravel"."user_searches" WHERE "null" = (SELECT MAX("null") FROM "airtravel"."user_searches");
```
6. PostgreSQL interprets `"null"` as a column identifier (not the NULL keyword), hence the error.
## Related Issues
- https://github.com/airbytehq/airbyte/issues/28262 - Empty table causing a failure in cursor based ctid (closed)
- https://github.com/airbytehq/airbyte/issues/28363 - ctid with cursor: Sync containing both incremental and full refresh streams will fail to sync (closed)
This appears to be a similar class of bug where streams without valid cursor fields are incorrectly processed through the cursor-based code path.
## Suggested Fix
The code should either:
1. Filter out streams without valid cursor fields from being processed by `getCursorBasedSyncStatusForStreams`
2. Add a null check in `getCursorBasedSyncStatusForStreams` to skip streams with null cursor fields
3. Throw a more descriptive error message instead of generating invalid SQL
## Workaround
Users can work around this by explicitly setting a cursor column (e.g., `created_at` or `updated_at`) for the affected streams and switching them to incremental sync mode.
---
*Reported by syed.khadeer@airbyte.io via Devin session: https://app.devin.ai/sessions/099414955b86437ea8cc19c38d0522b2*
コントリビューションガイド
評価
この issue はまだ評価されていません。