MIME-Version: 1.0
References: <CAGv0ivrBc=X6iaWA9Fng7u8D5BbM6EbcNy-4xF0DGFrAV+c2zw@mail.gmail.com>
In-Reply-To: <CAGv0ivrBc=X6iaWA9Fng7u8D5BbM6EbcNy-4xF0DGFrAV+c2zw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 6 Jan 2025 09:37:20 -0700
Message-ID: <CAKFQuwYYpyv_qqLXLKPqTKeY74bUR9XMXzAgUh=OPxYLqn=Ssw@mail.gmail.com>
Subject: Re: Query related to Logical Replication using test_decoding and unchanged-toast-datum
To: Kiran K V <kirankv.1982@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000044b8d062b0c4040"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYYpyv_qqLXLKPqTKeY74bUR9XMXzAgUh%3DOPxYLqn%3DSsw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Jan 6, 2025 at 7:21=E2=80=AFAM Kiran K V <kirankv.1982@gmail.com> w=
rote:

> could you please tell me whether PostgreSQL will truly log these values t=
o
> WAL or not ? If not, what will be entered into WAL for the JSON column if
> it remains unchanged?
>
>
Not quite sure what you are looking for but:

https://www.postgresql.org/docs/current/storage-toast.html#STORAGE-TOAST-ON=
DISK

"A pointer datum representing an out-of-line on-disk TOASTed value
therefore needs to store the OID of the TOAST table in which to look and
the OID of the specific value (its chunk_id). For convenience, pointer
datums also store the logical datum size (original uncompressed data
length), physical stored size (different if compression was applied), and
the compression method used, if any. Allowing for the varlena header bytes,
the total size of an on-disk TOAST pointer datum is therefore 18 bytes
regardless of the actual size of the represented value."

So the WAL of the main table data will contain 18bytes of data for that
column.  If the toast data really hasn't changed during the period in
question the WAL will not contain any data for the toast relation since no
changes to it were made.

David J.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Mon, Jan 6, 2025 at 7:21=E2=80=AFAM Kiran K V &lt;<a hr=
ef=3D"mailto:kirankv.1982@gmail.com">kirankv.1982@gmail.com</a>&gt; wrote:<=
/span></div></div><div class=3D"gmail_quote gmail_quote_container"><blockqu=
ote 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"><div>could you p=
lease tell me whether PostgreSQL will truly log these values to WAL or not =
? If not, what will be entered into WAL for the JSON column if it remains u=
nchanged?</div><div><br></div></div></blockquote><div><br></div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Not qui=
te sure what you are looking for but:</div><div class=3D"gmail_default" sty=
le=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail=
_default" style=3D"font-family:arial,helvetica,sans-serif"><a href=3D"https=
://www.postgresql.org/docs/current/storage-toast.html#STORAGE-TOAST-ONDISK"=
>https://www.postgresql.org/docs/current/storage-toast.html#STORAGE-TOAST-O=
NDISK</a></div><div class=3D"gmail_default" style=3D"font-family:arial,helv=
etica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-fami=
ly:arial,helvetica,sans-serif">&quot;A pointer datum representing an out-of=
-line on-disk TOASTed value therefore needs to store the OID of the TOAST t=
able in which to look and the OID of the specific value (its chunk_id). For=
 convenience, pointer datums also store the logical datum size (original un=
compressed data length), physical stored size (different if compression was=
 applied), and the compression method used, if any. Allowing for the varlen=
a header bytes, the total size of an on-disk TOAST pointer datum is therefo=
re 18 bytes regardless of the actual size of the represented value.&quot;</=
div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-=
serif"><br></div><div class=3D"gmail_default" style=3D"font-family:arial,he=
lvetica,sans-serif">So the WAL of the main table data will contain 18bytes=
=C2=A0of data for that column.=C2=A0 If the toast data really hasn&#39;t ch=
anged during the period in question the WAL=C2=A0will not contain any data =
for the toast relation since no changes to it were made.</div><div class=3D=
"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div>=
<div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-seri=
f">David J.</div><div class=3D"gmail_default" style=3D"font-family:arial,he=
lvetica,sans-serif"><br></div></div></div>

--000000000000044b8d062b0c4040--