Subject: Re: Historic snapshot doesn't track txns committed in BUILDING_SNAPSHOT state

From: Ajin Cherian <[email-masked]>
Date: Fri, 27 Jun 2025 20:29:53 +1000

On Fri, Jun 27, 2025 at 3:48 PM cca5507 <[email-masked]> wrote:
>
> Hi,
>
> I refactor the code and fix the git apply warning according to [1].
>
> Here are the new version patches.
>
> --
> Regards,
> ChangAo Chen
>
> [1] https://www.postgresql.org/message-id/Zrmh7X8jYCbFYXjH%40ip-10-97-1-34.eu-west-3.compute.internal

I see this problem is similar to the bug reported in [1], and your fix
also addresses the issue reported there. Although I like your approach
of tracking changes starting from the BUILDING_SNAPSHOT state, I’d
like to suggest an alternative.

While debugging that issue, my plan was not to track catalog changes
prior to SNAPBUILD_CONSISTENT, but instead to ensure we don’t use
snapshots built before SNAPBUILD_CONSISTENT, since we don’t track
catalog changes in those states. We should discard previously built
snapshots and rebuild them once we reach the SNAPBUILD_CONSISTENT
state. At that point, all necessary transactions would have been
committed, and builder->xmin would have advanced enough to decode all
transactions from then on.

The problem is that previously built snapshots hang around without the
latest xmin and xmax, and we tend to reuse them. We should ensure that
all txn->base_snapshot and builder->snapshot snapshots built in the
SNAPBUILD_FULL_SNAPSHOT state are rebuilt once we reach
SNAPBUILD_CONSISTENT. For this, we need to track when the snapshot was
built. There is already a field in ReorderBufferTXN -
'base_snapshot_lsn' which we can use. If base_snapshot_lsn <
builder->start_decoding_at, then we should rebuild the snapshot. Just
a thought.

regards,
Ajin Cherian
Fujitsu Australia

[1] - https://www.postgresql.org/message-id/18509-983f064d174ea880%40postgresql.org

---

From: "cca5507" <[email-masked]>
Date: Sat, 28 Jun 2025 11:44:52 +0800

Hi,

If I understand correctly, this may break the basic&nbsp;principle:

The aim is to build a&nbsp;snapshot that behaves the same as a freshly taken MVCC snapshot would have&nbsp;at the time the XLogRecord was generated.

--
Regards,
ChangAo Chen

------------------&nbsp;Original&nbsp;------------------
From:                                                                                                                        "Ajin Cherian"                                                                                    <[email-masked]&gt;;
Date:&nbsp;Fri, Jun 27, 2025 06:29 PM
To:&nbsp;"cca5507"<[email-masked]&gt;;
Cc:&nbsp;"Bertrand Drouvot"<[email-masked]&gt;;"Michael Paquier"<[email-masked]&gt;;"pgsql-hackers"<[email-masked]&gt;;
Subject:&nbsp;Re: Historic snapshot doesn't track txns committed in BUILDING_SNAPSHOT state

On Fri, Jun 27, 2025 at 3:48 PM cca5507 <[email-masked]&gt; wrote:
&gt;
&gt; Hi,
&gt;
&gt; I refactor the code and fix the git apply warning according to [1].
&gt;
&gt; Here are the new version patches.
&gt;
&gt; --
&gt; Regards,
&gt; ChangAo Chen
&gt;
&gt; [1] https://www.postgresql.org/message-id/Zrmh7X8jYCbFYXjH%40ip-10-97-1-34.eu-west-3.compute.internal

committed, and builder-&gt;xmin would have advanced enough to decode all

The problem is that previously built snapshots hang around without the
latest xmin and xmax, and we tend to reuse them. We should ensure that
all txn-&gt;base_snapshot and builder-&gt;snapshot snapshots built in the
SNAPBUILD_FULL_SNAPSHOT state are rebuilt once we reach
SNAPBUILD_CONSISTENT. For this, we need to track when the snapshot was
built. There is already a field in ReorderBufferTXN -
'base_snapshot_lsn' which we can use. If base_snapshot_lsn <
builder-&gt;start_decoding_at, then we should rebuild the snapshot. Just
a thought.

regards,
Ajin Cherian
Fujitsu Australia

---

From: Michael Paquier <[email-masked]>
Date: Wed, 2 Jul 2025 08:47:03 +0900

On Sat, Jun 28, 2025 at 11:44:52AM +0800, cca5507 wrote:
> The aim is to build a&nbsp;snapshot that behaves the same as a
> freshly taken MVCC snapshot would have&nbsp;at the time the
> XLogRecord was generated. 

Please note that we prefer bottom-posting when sending messages on
pgsql-hackers and the community mailing lists, as defined in this
link:
https://en.wikipedia.org/wiki/Posting_style#Bottom-posting

Top-posting, as you did in your last email, is breaking the logic and
flow of the discussion.

Some more useful documentation from the PostgreSQL wiki:
https://wiki.postgresql.org/wiki/Mailing_Lists
--
Michael

---

From: "cca5507" <[email-masked]>
Date: Fri, 21 Nov 2025 16:16:16 +0800

Hi all,

Attach the rebased patches.

Some useful link:

Discussion:
https://www.postgresql.org/message-id/flat/[email-masked]
https://www.postgresql.org/message-id/flat/[email-masked]
https://www.postgresql.org/message-id/flat/[email-masked]

Bug Report:
https://www.postgresql.org/message-id/flat/18509-983f064d174ea880%40postgresql.org
https://www.postgresql.org/message-id/flat/19109-4a9ce36715d69632%40postgresql.org

CF Entry:
https://commitfest.postgresql.org/patch/5029/

--
Regards,
ChangAo Chen