MIME-Version: 1.0
References: <19434-297bf2cbd8d2931a@postgresql.org> <CAKAnmm+csbrmkn5SFUd60SAAfe9P1+srbjuEPWvH+3XNCvDrwA@mail.gmail.com>
In-Reply-To: <CAKAnmm+csbrmkn5SFUd60SAAfe9P1+srbjuEPWvH+3XNCvDrwA@mail.gmail.com>
From: Tim McLaughlin <tim@gotab.io>
Date: Sun, 5 Apr 2026 08:41:08 -0400
Message-ID: <CAA6J38LaBbDa5+9kng8R=nBQksqvtFqwoPfE-0z8if4GhLp9ww@mail.gmail.com>
Subject: Re: BUG #19434: adding WHERE to a publication can cause UPDATEs and
 DELETEs to fail on the source table
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ecd155064eb5dd0b"
Archived-At: <https://www.postgresql.org/message-id/CAA6J38LaBbDa5%2B9kng8R%3DnBQksqvtFqwoPfE-0z8if4GhLp9ww%40mail.gmail.com>
Precedence: bulk

--000000000000ecd155064eb5dd0b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Greg,
I'm actually working on another project that uses CDC for fraud detection
and it occurred to me that it would be really great if there was an
explicit way to just add a column to the "important columns" in general
when the table is added to a publication.  I'm currently using a
pg_logical_embed_message to do it, but it would be a lot cleaner if there
was something like this:

ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname)
INCLUDE (account_id) WHERE (status =3D 'ACTIVE');

It would effectively then have account_id & status always included in the
NEW column.

Cheers,
Tim


On Mon, Mar 23, 2026 at 12:47=E2=80=AFPM Greg Sabino Mullane <htamfids@gmai=
l.com>
wrote:

> I don't think this is really a bug, more of a feature request /
> optimization. However, I do agree this is an important one. I don't see
> offhand why we can't append the where list to our existing list of
> important columns. There would be a few downsides, but none that would be
> worse than going full replica identity or creating a new index. I'll see
> about making a proof of concept patch and throwing it on -hackers. Will c=
c
> you on that.
>
> Cheers,
> Greg
>
>

--000000000000ecd155064eb5dd0b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Greg,</div><div>I&#39;m actually working on another p=
roject that uses CDC for fraud detection and it occurred to me that it woul=
d be really great if there was an explicit way to just add a column to the =
&quot;important columns&quot; in general when the table is added to a publi=
cation.=C2=A0 I&#39;m currently using a pg_logical_embed_message to do it, =
but it would be a lot cleaner if there was something like this:<br><pre cla=
ss=3D"gmail-programlisting" style=3D"box-sizing:border-box;font-family:mono=
space,monospace;font-size:14.4px;margin:1rem 0px;overflow:auto;color:rgb(0,=
0,0);background-color:rgb(248,249,250);border-radius:0.25rem;padding:0.8rem=
;border-color:rgb(206,212,218);border-style:solid;border-width:0.909091px">=
ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname) <br>IN=
CLUDE (account_id) WHERE (status =3D &#39;ACTIVE&#39;);</pre></div><div>It =
would effectively then have account_id &amp; status always included in the =
NEW column.</div><div><br></div><div><div dir=3D"ltr" class=3D"gmail_signat=
ure" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Cheers,<div>Tim</d=
iv></div></div></div><br></div><br><div class=3D"gmail_quote gmail_quote_co=
ntainer"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Mar 23, 2026 at 12:4=
7=E2=80=AFPM Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">=
htamfids@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><div dir=3D"ltr">I don&#39;t think this is really a bug, =
more of a feature request / optimization. However, I do agree this is an im=
portant one. I don&#39;t see offhand why we can&#39;t append the where list=
 to our existing list of important columns. There would be a few downsides,=
 but none that would be worse than going full replica identity or creating =
a new index. I&#39;ll see about making a proof of concept patch and throwin=
g it on -hackers. Will cc you on that.<div><br></div><div>Cheers,</div><div=
>Greg</div><div><br></div></div>
</blockquote></div>

--000000000000ecd155064eb5dd0b--