airbytehq / airbytehq/airbyte

[source-postgres] CDC sync loses backfilled values for newly added columns if the backfill occurs before schema refresh

オープン
#80,920 コメント 2 件 リアクション 0 件 担当者 0 名 GitHub で見る
area/connectors autoteam community connectors/source/postgres needs-triage team/extensibility team/use type/bug
主要言語
Python
スター
22.1k
フォーク
5.3k
PR マージ指標
PR 指標を取得中

説明

### Connector Name

source-postgres

### Connector Version

3.7.0

### What step the error happened?

During the sync

### Relevant information

We've encountered an issue when using PostgreSQL CDC to sync new column data to Snowflake.

Our migration process is typically:

1. Add a new column to an existing table.
2. Backfill the new column for existing rows using an UPDATE.
4. Accept schema changes in Airbyte after the db migration.
5. Continue syncing the table via CDC.

For example:

```
ALTER TABLE entitities ADD COLUMN foo TEXT;

UPDATE entitities
SET foo = ...
FROM magical_logic
WHERE foo IS NULL;
```

### Expected behavior

Once the new column has been added to the destination schema, the destination should eventually converge to the source state, including values populated by backfill updates.

### Actual behavior

The backfill updates are never reflected in the destination, leaving existing rows with NULL in the new column until a full refresh is performed. (New rows inserted after the migration have the correct value.)

This appears to happen when the backfill is performed before Airbyte has refreshed the source schema and detected the new column.

### Questions

- Is this expected behavior or a bug?
- If expected, is there a recommended way to perform schema evolution and backfills without requiring a full refresh?
- Would it be feasible for Airbyte to handle this scenario automatically (for example, by replaying buffered CDC events for newly discovered columns or triggering a targeted refresh)?

### Relevant log output

```shell

```

### Contribute

- [ ] Yes, I want to contribute

---
**Internal Tracking:** https://github.com/airbytehq/oncall/issues/13000

コントリビューションガイド

コントリビューションガイドを開く

評価

この issue はまだ評価されていません。

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。