search

transferwise / pipelinewise-tap-postgres

Singer.io Tap for PostgreSQL - PipelineWise compatible
https://transferwise.github.io/pipelinewise/
GNU Affero General Public License v3.0
41 stars 101 forks source link

Handle Postgres TOAST values and missing columns/values from wal2json #146

Open mvgijssel opened 2 years ago

mvgijssel commented 2 years ago

Describe the bug

We ran into the problem that particular columns ended up NULL in the data warehouse after logical replication updates. After diving into the problem we discovered that this happens because of TOAST values in Postgres. A good explanation can be found here https://debezium.io/blog/2019/10/08/handling-unchanged-postgres-toast-values/, but TL;DR Postgres can sometimes decide to not include a column in the logical replication message from wal2json if that particular column was not part of the UPDATE statement.

To Reproduce

Used the steps described here https://github.com/eulerto/wal2json/pull/99/files#diff-dc97f558087716ec3429b35c430504cbd91e93aa6e477188c289927632d01b6f to reproduce the problem.

Expected behavior The expectation is that if the message from wal2json contains a toast value and misses a column it does not set that column to NULL for the downstream singer target.

Your environment

Additional context Because HackerOne is using this tap in production we have already pushed a fix to our fork of tap-postgres in https://github.com/Hacker0x01/pipelinewise-tap-postgres/pull/1. The commit with the working fix for HackerOne is https://github.com/Hacker0x01/pipelinewise-tap-postgres/pull/1/commits/1584b8d321c0a9104cd6e65f8e1dfd8d8c7c1bac. This fix requires altering tables with REPLICA IDENTITY FULL.

mvgijssel commented 2 years ago

An alternative (and better) implementation of dealing with missing columns due to TOAST values is https://github.com/Hacker0x01/pipelinewise-tap-postgres/pull/1/commits/acd0b0fbae480e4233be27090dbe80df38bf72f7. Here we are outputting a new (partial) schema from the extractor to the loader for the single record that has missing columns.

If used together with an extractor like https://github.com/transferwise/pipelinewise-target-snowflake then the loader will try an upload a partial record. Partial records work fine in Snowflake with the Parquet file format (or with CSV file format ignoring the difference in columns).