Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19434: adding WHERE to a publication can cause UPDATEs and DELETEs to fail on the source table
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: tim@gotab.io
Reply-To: tim@gotab.io, pgsql-bugs@lists.postgresql.org
Date: Sun, 15 Mar 2026 14:05:49 +0000
Message-ID: <19434-297bf2cbd8d2931a@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19434-297bf2cbd8d2931a%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19434
Logged by:          Tim McLaughlin
Email address:      tim@gotab.io
PostgreSQL version: 18.3
Operating system:   all
Description:       =20

Adding a WHERE clause to a publication can cause UPDATEs and DELETEs to fail
on the source table if the filter columns aren't part of the replica
identity. While this is a documented behavior, I do think this is a bug and
I'll explain why.

The core problem is that REPLICA IDENTITY controls two unrelated things:
which columns identify a row on the subscriber, and which old column values
get written to WAL. Publication row filters need the second capability but
have nothing to do with the first. These two purposes should be separated.

The result can a bad failure mode (which I experienced catastrophically): a
DBA adds a WHERE clause to a publication =E2=80=94 a change that should onl=
y affect
what gets replicated =E2=80=94 and it breaks writes on the publisher. There=
's no
error at DDL time. The failure only appears when the application hits an
UPDATE or DELETE, potentially taking down production writes as it did in my
case.

The current workarounds both have unnecessary costs:
- REPLICA IDENTITY FULL writes every column's old value to WAL for every
change, increasing WAL volume far beyond what the filter actually needs.
- Creating a unique index that includes the filter columns adds storage and
maintenance overhead for an index that serves no query purpose.

I'd propose that when a publication has a WHERE clause, PostgreSQL
automatically includes the referenced columns' old values in WAL without
requiring a change to REPLICA IDENTITY. The additional columns being written
could be tracked in a new column on pg_publication_rel, making the behavior
transparent and inspectable.

This would preserve the existing REPLICA IDENTITY for their intended (and
semantically sensible) purpose while eliminating a non-obvious way to break
a production publisher.