airbytehq / airbytehq/airbyte

bug(source-postgres): Resumable full refresh generates invalid SQL with null cursor field

未关闭
#70,356 0 条评论 1 个 reaction 已指派 0 人 在 GitHub 查看
autoteam connectors/source/postgres team/db-dw-sources type/bug
主要语言
Python
星标
22.1k
派生
5.4k
平均合并
5 小时
30 天内合并 PR
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 还没有评估数据。

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。