Date: Fri, 7 Mar 2025 10:42:17 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>,
	Andres Freund <andres@anarazel.de>,
	Peter Smith <smithpb2250@gmail.com>,
	Peter Eisentraut <peter@eisentraut.org>,
	shveta malik <shveta.malik@gmail.com>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Add contrib/pg_logicalsnapinspect
Message-ID: <Z8rNiT6H2/Mdb60v@ip-10-97-1-34.eu-west-3.compute.internal>
References: 
 <CAHut+PsYdTW4+U+fioh9_yO8iT6RnY=PquzfCZu7zRU6Qg6W7g@mail.gmail.com>
 <Zwy4ys76aPcoVE/I@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAD21AoDuO87MFrPJFwtmRi=cFhDGac0zq7sq5pdEhVi7zUvbEg@mail.gmail.com>
 <5qbxud4pvnvmtuoi7weiizm5hmumxaeohx4vztfhrwlfhyz6rj@buh4435mllwo>
 <CAD21AoBM6vAcPGR-ng0nqGG0yemR_ufdg3+v3gkPa6Nc2ntnrA@mail.gmail.com>
 <Z8f6dQPR67ahM2DN@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA4eK1L0bZbZOr8-VNFXDfqaTwggdf_-HTAsneui+_ozMTvZQg@mail.gmail.com>
 <Z8g9/GeoXv/gIL60@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAD21AoBkdiq6Te7D_LtKDDB2WsZWBKWqWNVguhy9R_xAMf_xDg@mail.gmail.com>
 <CAA4eK1LGV8PME4KfZanF16gE1HDb+nPn06TTxv1OtV0466oJ-w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="dbJN0IdEymweUBIK"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAA4eK1LGV8PME4KfZanF16gE1HDb+nPn06TTxv1OtV0466oJ-w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Z8rNiT6H2/Mdb60v%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk


--dbJN0IdEymweUBIK
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Hi,

On Fri, Mar 07, 2025 at 10:26:23AM +0530, Amit Kapila wrote:
> On Fri, Mar 7, 2025 at 3:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Wed, Mar 5, 2025 at 4:05 AM Bertrand Drouvot
> > <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > Hi,
> > >
> > > On Wed, Mar 05, 2025 at 02:42:15PM +0530, Amit Kapila wrote:
> > > > On Wed, Mar 5, 2025 at 12:47 PM Bertrand Drouvot
> > > > <bertranddrouvot.pg@gmail.com> wrote:
> > > > >
> > > > > Agree, PFA a patch doing so.
> > > > >
> > > >
> > > > It would be better if you could add a few comments atop the
> > > > permutation line to explain the working of the test.
> > >
> > > yeah makes sense. Done in the attached, and bonus point I realized that the
> > > test could be simplified (so, removing useless steps in passing).
> > >
> >
> > Thank you for the patch.
> >
> > The new simplified test case can be pretty-formatted as:
> >
> > init
> > begin
> > savepoint
> > truncate
> >                 checkpoint-1
> >                 get_changes-1
> > commit
> >                 checkpoint-2
> >                 get_changes-2
> >                 info_catchange check
> >                 info_committed check
> >                 meta check

Yes.

> > IIUC if another checkpoint happens between get_change-2 and the
> > subsequent checks, the first snapshot would be removed during the
> > checkpoint, resulting in a test failure.

Good catch! Yeah you're right, thanks!

> I think we could check the
> > snapshot files while one transaction keeps open. The more simplified
> > test case would be:
> >
> > init
> > begin
> > savepoint
> > insert(cat-change)
> >                 begin
> >                 insert(cat-change)
> >                 commit
> >                 checkpoint
> >                 get_changes
> >                 info_catchange check
> >                 info_committed check
> >                 meta check
> > commit
> >
> > In this test case, we would have at least one serialized snapshot that
> > has both cat-changes and committed txns. What do you think?

Indeed, I think that would prevent snapshots to be removed.

The attached ends up doing:

init
begin
savepoint
truncate table1
		       create table table2
               checkpoint
               get_changes
               info check
               meta check
commit

As the 2 ongoing catalog changes and the committed catalog change are part of the
same snapshot, then I grouped the catchanges and committed changes checks in the
same "info check".

> Your proposed change in the test sounds better than what we have now
> but I think we should also avoid autovacuum to perform analyze as that
> may add additional counts. For test_decoding, we keep
> autovacuum_naptime = 1d in logical.conf file, we can either use the
> same here or simply keep autovacuum off.

When writing the attached, I initially added extra paranoia in the tests by
using ">=", does that also address your autovacuum concern?

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

--dbJN0IdEymweUBIK
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="v3-0001-Modify-pg_logicalinspect-isolation-test.patch"