Logical replication row filter loses unchanged toasted columns

From: Shinya Kato <shinya11(dot)kato(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Logical replication row filter loses unchanged toasted columns
Date: 2026-08-12 03:22:33
Message-ID: CAOzEurQaGBrDu8hFw+p16_uf_+b94A276fYgzwUOK5X-bSX+jg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

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. This has
been there since row filters were added in commit 52e4f0cd472, so 15
and up are affected.

Reproduction:
```
-- publisher
CREATE TABLE t (id int PRIMARY KEY, body text);
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));

-- subscriber
CREATE TABLE t (id int PRIMARY KEY, body text);
CREATE SUBSCRIPTION s CONNECTION 'dbname=postgres port=5432' PUBLICATION p;

-- publisher
UPDATE t SET id = 7; -- moves the row into the filter

-- subscriber
\pset null '(null)'
SELECT id, body FROM t;
id | body
----+--------
7 | (null)
(1 row)
```
pgoutput_row_filter() copies unchanged out-of-line values over from
the old tuple, but unless the replica identity is FULL the old tuple
carries only the replica identity columns. Any other column is still
an external on-disk pointer, so logicalrep_write_tuple() sends it as
LOGICALREP_COLUMN_UNCHANGED, which an INSERT cannot express, and
slot_store_data() stores a NULL. Where the column is NOT NULL, the
apply worker fails with a constraint violation and replication stops.

We cannot simply fill the value in. As Petr put it when
LOGICALREP_COLUMN_UNCHANGED was being discussed [1], such values "are
not written to WAL nor accessible via historic snapshot", so the
output plugin never sees them.

I see three ways to deal with this.

Option A: detect the missing value in pgoutput_row_filter() and raise
an error naming the table and the column, trading silent data loss for
a loud failure. The catch is that the error is not recoverable. Once
the change is in WAL, neither ALTER TABLE ... REPLICA IDENTITY FULL
nor dropping the row filter helps, because decoding uses the historic
catalog snapshot from the time of the change. The only way forward is
pg_replication_slot_advance() or recreating the subscription.

Option B: when a table belongs to a publication with a row filter,
make heap_update() log the whole old tuple, as it already does for
REPLICA IDENTITY FULL. The existing copy loop then finds the value,
the INSERT is complete, and no error is needed. This is the real fix,
but every UPDATE of such a table writes the unchanged out-of-line
value to WAL even when no transformation happens, which can be a large
regression. It also needs a new field in PublicationDesc, so I do not
think it can be back-patched.

Option C: document the restriction and leave the behavior alone. This
is the only option that changes nothing on the back branches, but the
value keeps disappearing without any warning.

I lean towards A because losing data silently seems worse than
stopping, but the unrecoverable error bothers me. Which approach do
you prefer, and should the fix be back-patched?

[1] https://www.postgresql.org/message-id/1f3a3b7d-3ea2-630c-1b99-368df3fdecdf%402ndquadrant.com

--
Shinya Kato
NTT OSS Center

Browse pgsql-hackers by date

  From Date Subject
Next Message Shinya Kato 2026-08-12 03:28:18 Re: Fix contradictory comment for pgstat_slru_flush_cb()
Previous Message Peter Geoghegan 2026-08-12 03:21:30 Re: index prefetching