RE: Logical replication row filter loses unchanged toasted columns

On Wednesday, August 12, 2026 4:05 PM Hayato Kuroda (Fujitsu) <kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
> > I found a bug in the row filter's UPDATE to INSERT transformation. An
> > unchanged column that is stored out-of-line silently becomes NULL on
> > the subscriber, unless it is part of the replica identity.
>
> I could also reproduce the failure with your reproducer.
>
> > ALTER TABLE t ALTER COLUMN body SET STORAGE EXTERNAL; CREATE
> > PUBLICATION p FOR TABLE t WHERE (id = 7); INSERT INTO t VALUES (3,
> > repeat('a', 5000));
>
> Note that I could reproduce without setting the storage parameter to
> EXTERNAL.
> E.g., we can put string which has lower compression rate, like below.
>
> ```
> CREATE EXTENSION pgcrypto;
> INSERT INTO t SELECT 3, string_agg(encode(gen_random_bytes(1000), 'hex'),
> '') FROM generate_series(1, 5); ```
> > I see three ways to deal with this.
>
> Option B would have performance regressions not only for logical replication
> but also for normal workloads. We need to generate for narrower cases, e.g.,
> check the filtering rule and generate WAL for unchanged toasted columns, if
> we want to choose this. I'm not sure it's possible or how engineering would be
> needed though.
>
> So, I prefer to 1) implement option A for all branches first, and 2) investigate
> option B separately. Regarding the C, it can be chosen if the option A needs
> lots of codes.

I feel catching such an unchanged toasted column issue during INSERT in the
apply worker could be better. The apply worker provides native context about the
transaction and the action being replayed, making the error easier to diagnose.
Erroring in the apply worker also gives users more flexibility to resolve the
issue, for example, they could define a temporary trigger on the target table to
skip the conflict or fill in the toasted value manually (after querying it from
the publisher), or skip the whole transaction using ALTER SUB SKIP, or disable
the subscription for now and analyze the issue and resolve later.

I also thought of reporting ERROR during DML, like putting this in
CheckCmdReplicaIdentity, but we can't tell at that point whether the tuple
being updated has a toasted column. And we also could not simply error out in
CheckCmdReplicaIdentity for any column that could be toasted, because lots of
columns types (text, varchar ...) is default with EXTENDED storage that can
potentially be toasted, so that would be too broad.

The only feasible place for DML error is at a lower level, like heap_update,
where we can error out if an unchanged toasted column (that is not part of the
replica identity) is being updated on a table published with a row filter.
However, this might overkill in cases where the row filter would not convert the
update to an INSERT, and I'm not sure we want to evaluate the row filter
expression during DML to do more detailed check. So I think it's not a great
idea to do it in DML.

Beyond this specific row filter issue, I'm thinking about a more general
problem: when an unchanged toasted column is not logged in the new tuple, the
subscriber cannot perform proper conflict resolution. For example, if an update
hits an update_exists conflict and the user wants to keep the remote change
(converting the UPDATE to an INSERT), without the unchanged toasted value, the
resolution lacks the data needed for the INSERT. I think we may need to support
logging additional columns beyond the replica identity in the future to address
this.

BTW, other CDC solutions also suffer from the lack of unchanged toasted
columns - I've seen several blogs mention this as a limitation [1][2].

So, I think after applying the fix to report an ERROR, it would be worth
implementing an additional WAL logging feature on top of it, allowing users to specify
which columns should be logged in WAL for logical decoding and replication. I
can see several use cases for this: 1) Allowing users to add non-RI columns to
publication row filter expressions. 2) Helping with conflict resolution. 3)
Giving subscriber replication workers more column data for analysis, such as
detecting whether two transactions modify the same subscriber-only unique index
and enabling parallel apply if not. 4) Making it easier for other CDC solutions
to handle missing values.

[1]https://www.morling.dev/blog/backfilling-postgres-toast-columns-debezium-change-events/
[2]https://clickhouse.com/docs/integrations/clickpipes/postgres/toast

Best Regards,
Zhijie Hou