Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u3xE2-005Vkr-Qt for pgsql-hackers@arkaria.postgresql.org; Sun, 13 Apr 2025 13:18:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u3xE0-006wLI-If for pgsql-hackers@arkaria.postgresql.org; Sun, 13 Apr 2025 13:18:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u3xDz-006wKv-Mk for pgsql-hackers@lists.postgresql.org; Sun, 13 Apr 2025 13:18:17 +0000 Received: from m16.mail.163.com ([117.135.210.2]) by makus.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1u3xDt-0007QC-1l for pgsql-hackers@postgresql.org; Sun, 13 Apr 2025 13:18:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=From:Message-Id:Content-Type:Mime-Version:Subject: Date; bh=lnQgj3ulI/WeMnpZRWCQ/BJN69xDEBVecAWjp3OX4M0=; b=M+jCPas MDZYj7M7o1IUEwdPBt4lSo2RtreBcgh7Xb0SZ49zFA1++lu9JJTdyAfj7duzJuEP +qGTA6YgEgryV42NazM+Jr4J207/HjcuZ942d8HUdJM2WmWa7S8BpVE7cr3asCZb e/Op5g2W/6RZslDw13H9l2VaBd4hjs8A3mTo= Received: from smtpclient.apple (unknown []) by gzsmtp3 (Coremail) with SMTP id PigvCgCnPcp6uftnY5gjAQ--.41158S2; Sun, 13 Apr 2025 21:17:47 +0800 (CST) From: =?utf-8?B?6LS+5piO5Lyf?= Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_D37A304B-7A76-4913-AAEF-1F04EA6A0229" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3818.100.11.1.3\)) Subject: Re: CSN snapshots in hot standby Date: Sun, 13 Apr 2025 21:17:36 +0800 In-Reply-To: <355C0849-820C-4373-9CC9-9A257C661C3A@163.com> Cc: John Naylor , pgsql-hackers , Andres Freund , "Andrey M. Borodin" , Kirill Reshke To: Heikki Linnakangas References: <08da26cc-95ef-4c0e-9573-8b930f80ce27@iki.fi> <53C9CD5A-4403-4FF0-9F00-8B8A00F922A7@yandex-team.ru> <7cd97248-fd04-4fb2-9f7f-096c1d7660e8@iki.fi> <718d1788-b058-40e6-bc37-8f15612b5646@iki.fi> <80f254d3-8ee9-4cde-a7e3-ee99998154da@iki.fi> <355C0849-820C-4373-9CC9-9A257C661C3A@163.com> X-Mailer: Apple Mail (2.3818.100.11.1.3) X-CM-TRANSID: PigvCgCnPcp6uftnY5gjAQ--.41158S2 X-Coremail-Antispam: 1Uf129KBjDUn29KB7ZKAUJUUUUU529EdanIXcx71UUUUU7v73 VFW2AGmfu7bjvjm3AaLaJ3UbIYCTnIWIevJa73UjIFyTuYvjxUsPrcUUUUU X-Originating-IP: [60.25.59.51] X-CM-SenderInfo: 5zhlimiywqmjqs6rljoofrz/1tbiOgcuGWf7sx6zbwAAsn List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D37A304B-7A76-4913-AAEF-1F04EA6A0229 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi all, Apologies =E2=80=94 the patch I sent earlier did not appear as expected = on the mailing list archives because of wrong attachment style.=20 I'll resend it properly as an inline patch shortly. Thanks for your understanding! Best regards, =20 Mingwei Jia --Apple-Mail=_D37A304B-7A76-4913-AAEF-1F04EA6A0229 Content-Disposition: attachment; filename=v7-0013-use-clog-in-XidInMVCCSnapshot.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v7-0013-use-clog-in-XidInMVCCSnapshot.patch" Content-Transfer-Encoding: quoted-printable =46rom=205cbb8e5806a444df04ed2f54d1c45f4aa1ea8f7b=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Mingwei=20Jia=20=0ADate:=20= Sat,=2012=20Apr=202025=2021:10:44=20+0800=0ASubject:=20[PATCH]=20If=20= the=20checkpoint's=20oldestActiveXid=20at=20standby=20startup=20is=0A=20= less=20than=20nextXid,=20then=20the=20visibility=20of=20transactions=20= within=20that=20range=0A=20should=20be=20determined=20using=20the=20= CLOG.=20This=20is=20because=20there=20may=20be=0A=20transactions=20in=20= that=20range=20that=20have=20already=20committed=20on=20the=20primary,=20= and=0A=20these=20committed=20transactions=20will=20not=20be=20replayed=20= again=20on=20the=20standby.=0A=0A---=0A=20= src/backend/access/transam/xlog.c=20=20=20=20=20=20=20=20=20=20=20=20=20= |=20=202=20++=0A=20src/backend/storage/ipc/procarray.c=20=20=20=20=20=20=20= =20=20=20=20|=20=204=20+++=0A=20src/backend/utils/time/snapmgr.c=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=2012=20++++++++=0A=20= src/include/access/transam.h=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20|=20=202=20++=0A=20src/include/storage/procarray.h=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20|=20=202=20++=0A=20= .../modules/test_misc/t/008_csnstandby.pl=20=20=20=20=20|=2030=20= +++++++++++++++++++=0A=206=20files=20changed,=2052=20insertions(+)=0A=20= create=20mode=20100644=20src/test/modules/test_misc/t/008_csnstandby.pl=0A= =0Adiff=20--git=20a/src/backend/access/transam/xlog.c=20= b/src/backend/access/transam/xlog.c=0Aindex=20deb2cd1883c..2796458491d=20= 100644=0A---=20a/src/backend/access/transam/xlog.c=0A+++=20= b/src/backend/access/transam/xlog.c=0A@@=20-5565,6=20+5565,8=20@@=20= StartupXLOG(void)=0A=20=09TransamVariables->nextXid=20=3D=20= checkPoint.nextXid;=0A=20=09TransamVariables->nextOid=20=3D=20= checkPoint.nextOid;=0A=20=09TransamVariables->oidCount=20=3D=200;=0A+=09= TransamVariables->nextXidStandbyStart=20=3D=0A+=09=09=09=09=09= XidFromFullTransactionId(checkPoint.nextXid);=0A=20=09= MultiXactSetNextMXact(checkPoint.nextMulti,=20= checkPoint.nextMultiOffset);=0A=20=09= AdvanceOldestClogXid(checkPoint.oldestXid);=0A=20=09= SetTransactionIdLimit(checkPoint.oldestXid,=20checkPoint.oldestXidDB);=0A= diff=20--git=20a/src/backend/storage/ipc/procarray.c=20= b/src/backend/storage/ipc/procarray.c=0Aindex=20c82e8d8c438..d4c67512c56=20= 100644=0A---=20a/src/backend/storage/ipc/procarray.c=0A+++=20= b/src/backend/storage/ipc/procarray.c=0A@@=20-962,6=20+962,10=20@@=20= ProcArrayUpdateOldestRunningXid(TransactionId=20oldestRunningXID)=0A=20=09= procArray->oldest_running_primary_xid=20=3D=20oldestRunningXID;=0A=20=09= LWLockRelease(ProcArrayLock);=0A=20}=0A+TransactionId=20= GetOldestRunningXid(void)=0A+{=0A+=09return=20=09= procArray->oldest_running_primary_xid;=0A+}=0A=20=0A=20=0A=20/*=0Adiff=20= --git=20a/src/backend/utils/time/snapmgr.c=20= b/src/backend/utils/time/snapmgr.c=0Aindex=20df9e8ba37f4..0ebcb0af835=20= 100644=0A---=20a/src/backend/utils/time/snapmgr.c=0A+++=20= b/src/backend/utils/time/snapmgr.c=0A@@=20-1969,6=20+1969,18=20@@=20= XidInMVCCSnapshot(TransactionId=20xid,=20Snapshot=20snapshot)=0A=20=09=09= uint32=09=09cache_idx=20=3D=20snapshot->xmax=20-=20xid;=0A=20=09=09= uint64=09=09wordno=20=3D=20cache_idx=20/=20= INPROGRESS_CACHE_XIDS_PER_WORD;=0A=20=09=09uint64=09=09slotno=20=3D=20= (cache_idx=20%=20INPROGRESS_CACHE_XIDS_PER_WORD)=20*=20= INPROGRESS_CACHE_BITS;=0A+=09=09TransactionId=20nextXidStart=20=3D=20= TransamVariables->nextXidStandbyStart;=0A+=09=09TransactionId=20= oldestRunning=20=3D=20GetOldestRunningXid();=0A+=0A+=09=09if=20= (TransactionIdPrecedes(oldestRunning,=20nextXidStart)=0A+=09=09=09&&=20= TransactionIdPrecedes(xid,=20nextXidStart))=0A+=09=09{=0A+=09=09=09if=20= (TransactionIdDidCommit(xid)=20||=20TransactionIdDidAbort(xid))=0A+=09=09= =09{=0A+=09=09=09=09return=20false;=0A+=09=09=09}=0A+=09=09=09return=20= true;=0A+=09=09}=0A=20=0A=20=09=09if=20(snapshot->inprogress_cache)=0A=20= =09=09{=0Adiff=20--git=20a/src/include/access/transam.h=20= b/src/include/access/transam.h=0Aindex=20a7054fe11cd..a75d66f4d40=20= 100644=0A---=20a/src/include/access/transam.h=0A+++=20= b/src/include/access/transam.h=0A@@=20-240,6=20+240,8=20@@=20typedef=20= struct=20TransamVariablesData=0A=20=0A=20=09/*=20During=20recovery,=20= LSN=20of=20latest=20replayed=20commit=20record=20*/=0A=20=09XLogRecPtr=09= latestCommitLSN;=0A+=09/*=20checkpoint`s=20next=20xid=20when=20= hot-standby=20start=20*/=0A+=09TransactionId=20nextXidStandbyStart;=0A=20= =0A=20=09/*=0A=20=09=20*=20Number=20of=20top-level=20transactions=20with=20= xids=20(i.e.=20which=20may=20have=0Adiff=20--git=20= a/src/include/storage/procarray.h=20b/src/include/storage/procarray.h=0A= index=20de74fce24e4..a4129de8101=20100644=0A---=20= a/src/include/storage/procarray.h=0A+++=20= b/src/include/storage/procarray.h=0A@@=20-29,6=20+29,8=20@@=20extern=20= void=20ProcArrayEndTransaction(PGPROC=20*proc,=20TransactionId=20= latestXid);=0A=20extern=20void=20ProcArrayClearTransaction(PGPROC=20= *proc);=0A=20=0A=20extern=20void=20= ProcArrayUpdateOldestRunningXid(TransactionId=20oldestRunningXID);=0A+=0A= +extern=20TransactionId=20GetOldestRunningXid(void);=0A=20extern=20void=20= ProcArrayInitRecovery(TransactionId=20initializedUptoXID);=0A=20=0A=20= extern=20void=20RecordKnownAssignedTransactionIds(TransactionId=20xid);=0A= diff=20--git=20a/src/test/modules/test_misc/t/008_csnstandby.pl=20= b/src/test/modules/test_misc/t/008_csnstandby.pl=0Anew=20file=20mode=20= 100644=0Aindex=2000000000000..98708490d91=0A---=20/dev/null=0A+++=20= b/src/test/modules/test_misc/t/008_csnstandby.pl=0A@@=20-0,0=20+1,30=20= @@=0A+=0A+use=20strict;=0A+use=20warnings=20FATAL=20=3D>=20'all';=0A+use=20= PostgreSQL::Test::Cluster;=0A+use=20PostgreSQL::Test::Utils;=0A+use=20= Test::More;=0A+=0A+my=20$primary=20=3D=20= PostgreSQL::Test::Cluster->new('primary');=0A+$primary->init();=0A= +$primary->append_conf('postgresql.conf',=20'max_wal_senders=20=3D=20= 5');=0A+$primary->append_conf('postgresql.conf',=20'wal_level=3Dreplica');= =0A+$primary->start;=0A+my=20$count=20=3D=20'';=0A= +$primary->safe_psql('postgres',=20'create=20table=20t1(i=20int,=20j=20= int)');=0A+=0A+my=20$primary_a=20=3D=20=20= $primary->background_psql('postgres',=20on_error_die=20=3D>=201);=0A= +$primary_a->query_safe("begin");=0A+$primary_a->query_safe("insert=20= into=20t1=20values(1,1)");=0A+$primary->safe_psql('postgres',=20'insert=20= into=20t1=20values(2,1)');=0A+$primary->backup('bkp');=0A+=0A+my=20= $replica=20=3D=20PostgreSQL::Test::Cluster->new('replica');=0A= +$replica->init_from_backup($primary,=20'bkp',=20has_streaming=20=3D>=20= 1);=0A+$replica->start;=0A+=0A+$count=20=3D=20= $replica->safe_psql('postgres',=20"select=20count(*)=20from=20t1");=0A= +is($count,=20'1',=20"get=20right=20visiablity=20before=20primary=20= checkpoint=20in=20hot=20standby");=0A+=0A+done_testing();=0A+=0A--=0A= 2.45.0=0A=0A= --Apple-Mail=_D37A304B-7A76-4913-AAEF-1F04EA6A0229--