public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19434: adding WHERE to a publication can cause UPDATEs and DELETEs to fail on the source table
Date: Sun, 15 Mar 2026 14:05:49 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19434
Logged by:          Tim McLaughlin
Email address:      [email protected]
PostgreSQL version: 18.3
Operating system:   all
Description:        

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 — a change that should only affect
what gets replicated — 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.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: BUG #19434: adding WHERE to a publication can cause UPDATEs and DELETEs to fail on the source table
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox