MIME-Version: 1.0
References: <CADTnmPjcbW07j26AxdqAsMbSJEAdHtva7Kdazmo99twuF59whw@mail.gmail.com>
In-Reply-To: <CADTnmPjcbW07j26AxdqAsMbSJEAdHtva7Kdazmo99twuF59whw@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 11 Aug 2025 11:37:26 -0400
Message-ID: <CAKAnmm+JEf7FWAKW4KZoERsyU9SLZg86YMDUNirtUrKnWTe7eg@mail.gmail.com>
Subject: Re: Can postgres replication slot using pgoutput release multiple CDC
 records for a single update to a particular row
To: Akashkiran Shivakumar <akashkiran.foss@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006b1fab063c18b5d1"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BJEf7FWAKW4KZoERsyU9SLZg86YMDUNirtUrKnWTe7eg%40mail.gmail.com>
Precedence: bulk

--0000000000006b1fab063c18b5d1
Content-Type: text/plain; charset="UTF-8"

>
> possible that a single update statement could spill over as multiple CDC
> update records by pgoutput / postgres ?


Basically, no. If I understand your questions correctly, the answer seems
to point to your application making multiple updates, which will lead to
multiple UPDATE records in the WAL, and thus multiple UPDATEs via pgoutput.
But the basic mapping will still be one heap update = one WAL entry = one
pgoutput update. (It would be good to get familiar with the pg_waldump
program and examine the WAL yourself to get an idea of the exact changes
that are generated for various situations.) While there are cases in which
a single SQL update can generate multiple WAL entries (e.g. indexes,
TOAST), each individual update is always going to generate at least one WAL
record. Due to the way that Postgres works, each update gets put into WAL
immediately - Postgres cannot merge updates that happen to occur in the
same transaction. Nor does pgoutput combine those. So even though at the
end of the transaction a single row gets changed at the logical level,
there can be quite a number of changes at the WAL level. If you want to
smush together pgoutput's updates into a single call, you are welcome to do
so, of course - as long as it's in the same transaction. But I'd not do
that, as you are now messing with the order of events as they happened on
your primary. In other words, there may have been a reason it was updated
col by col on the primary, and if that bothers you, the primary is the
place to fix it, not stitching it together after the fact.

Cheers,
Greg

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

<div dir=3D"ltr"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">possible=
 that a single update statement could spill over as multiple CDC update=C2=
=A0records=C2=A0by pgoutput=C2=A0/ postgres=C2=A0?=C2=A0</blockquote><div><=
br></div><div>Basically, no. If I understand your=C2=A0questions correctly,=
 the=C2=A0answer seems to point to your application making multiple updates=
, which will lead to multiple UPDATE records in the WAL, and thus multiple =
UPDATEs via pgoutput. But the basic mapping will still be one heap update =
=3D one WAL entry =3D one pgoutput=C2=A0update. (It would be good to get fa=
miliar with the pg_waldump program and examine the WAL yourself to get an i=
dea of the exact changes that are generated for various situations.) While =
there are cases in which a single SQL update can generate multiple WAL entr=
ies (e.g. indexes, TOAST), each individual update is always going to genera=
te at least one WAL record. Due to the way that Postgres works, each update=
 gets put into WAL immediately - Postgres cannot merge updates that happen =
to occur in the same transaction. Nor does pgoutput=C2=A0combine those. So =
even though at the end of the transaction a single row gets changed at the =
logical level, there can be quite a number of changes at the WAL level. If =
you want to smush together pgoutput&#39;s updates into a single call, you a=
re welcome to do so, of course - as long as it&#39;s in the same transactio=
n. But I&#39;d not do that, as you are now messing with the order of events=
 as they happened on your primary. In other words, there may have been a re=
ason it was updated col by col on the primary, and if that bothers you, the=
 primary is the place to fix it, not stitching it together after the fact.<=
/div><div><br></div><div><div><div><div>Cheers,</div><div>Greg</div></div><=
/div><div><br></div></div></div>

--0000000000006b1fab063c18b5d1--