Issue with toast column in cdc apply for test_decoding

We are using only the follow the command with test_decoding to capture cdc from aws rds postgres 14.5 to aws rds postgres 14.5. During the prefect phase everything works perfectly but when i enable the apply process. We are observing that TOAST columns that are unchanged are handled differently in test_decoding which is resulting the apply process failing. 'unchanged-toast-datum' seems to be the cause of issue. Reference: https://debezium.io/blog/2019/10/08/handling-unchanged-postgres-toast-values/

Postgres result status is PGRES_FATAL_ERROR
21:35:33.920 11 ERROR  pgsql.c:2028              [TARGET 12277] ERROR:  invalid input syntax for type json
21:35:33.920 11 ERROR  pgsql.c:2028              [TARGET 12277] DETAIL:  Token "unchanged" is invalid.
21:35:33.920 11 ERROR  pgsql.c:2028              [TARGET 12277] CONTEXT:  JSON data, line 1: unchanged...
21:35:33.920 11 ERROR  pgsql.c:2028              [TARGET 12277] unnamed portal parameter $16 = '...'
21:35:33.920 11 ERROR  pgsql.c:2039              [TARGET 12277] SQL query: EXECUTE 4756fb82;
21:35:33.920 11 ERROR  pgsql.c:2047              [TARGET 12277] SQL params: '###', '###', '## - ### ##', NULL, NULL, NULL, NULL, 'success', '###', NULL, '2024-03-01 18:09:24.323412', '2024-03-01 18:12:15.94', '10000.00', '####', NULL, 'unchanged-toast-datum', 
21:35:33.920 11 ERROR  ld_apply.c:523            Failed to apply SQL from file "/dbhome/tmp//cdc/00000001000074990000000D.sql", see above for details

Hi @dimitri and @marikkan-microsoft,

We got different errors during migration from On-Premises -

Migration-1

2024-07-16T03:59:44.205Z    testdb  153 follow.c    NOTICE               Starting the catchup sub-process
2024-07-16T03:59:45.785Z    testdb  153 ld_apply.c  INFO                 Replaying changes from LSN 59D/90001E50
2024-07-16T14:21:00.570Z    testdb  153 pgsql.c     ERROR                [TARGET 7741] [22P02] ERROR:  malformed array literal: "unchanged-toast-datum"
2024-07-16T14:21:00.570Z    testdb  153 pgsql.c     ERROR                [TARGET 7741] DETAIL:  Array value must start with "{" or dimension information.
2024-07-16T14:21:00.570Z    testdb  153 pgsql.c     ERROR                [TARGET 7741] CONTEXT:  unnamed portal parameter $8 = '...'
2024-07-16T14:21:00.577Z    testdb  153 pgsql.c     ERROR                [TARGET 7741] Context: Failed to receive pipeline sync

Migration-2

2024-07-29T06:23:08.308Z    testdb-production   56  follow.c    NOTICE               Starting the catchup sub-process
2024-07-29T06:23:08.309Z    testdb-production   56  ld_apply.c  INFO                 Waiting until the pgcopydb sentinel apply is enabled
2024-07-29T07:51:38.830Z    testdb-production   56  ld_apply.c  INFO                 The pgcopydb sentinel has enabled applying changes
2024-07-29T07:51:38.890Z    testdb-production   56  ld_apply.c  INFO                 Setting up previous LSN from replication origin "azpgmigorigin_430618_20240729_062307" progress at 965/9DF877C8
2024-07-29T07:51:38.890Z    testdb-production   56  ld_apply.c  INFO                 Catchup-up with changes from LSN 965/9DF877C8
2024-07-29T07:51:38.893Z    testdb-production   56  ld_apply.c  INFO                 Replaying changes from file "/datadrive/migrations_data/a8f71f36-4e36-4471-ae47-6012f5082229/oid-430618/cdc/00000004000009650000009D.sql"
2024-07-29T07:51:38.979Z    testdb-production   56  pgsql.c     ERROR                [TARGET 561128] [22P02] ERROR:  invalid input syntax for type json
2024-07-29T07:51:38.979Z    testdb-production   56  pgsql.c     ERROR                [TARGET 561128] DETAIL:  Token "unchanged" is invalid.
2024-07-29T07:51:38.979Z    testdb-production   56  pgsql.c     ERROR                [TARGET 561128] CONTEXT:  JSON data, line 1: unchanged...
2024-07-29T07:51:38.979Z    testdb-production   56  pgsql.c     ERROR                [TARGET 561128] Context: Failed to receive pipeline sync

dimitri / pgcopydb

Issue with toast column in cdc apply for test_decoding #710