Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 17 Sep 2025 12:32:40 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Kouber Saparev <kouber@gmail.com>
Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: BF mamba failure
Message-ID: <aMor2AHvdbZWTwCD@paquier.xyz>
References: <ZzXL7WkzGyhxJljW@ip-10-97-1-34.eu-west-3.compute.internal>
 <ZzbR5Eu66lP9rt50@paquier.xyz>
 <CAN4RuQvYth942J2+FcLmJKgdpq6fE5eqyFvb_PuskxF2eL=Wzg@mail.gmail.com>
 <aJPiwtnsuh9yfFnz@paquier.xyz>
 <CAN4RuQt_Sr9h2NG0-fr7XSmU=DeHkzTeysCuOAWhWrkuhw8Puw@mail.gmail.com>
 <CAN4RuQs6ht4T7ph8NbxHZ+NmDmTrvczAxpyZmcqk+74tzsciuw@mail.gmail.com>
 <aMIJozPEP5OLI3Yj@paquier.xyz>
 <CAN4RuQv3OTJ1Owqn2U+7ZbvL-N-nZYAUjF_NG8o12ayFN-=0GQ@mail.gmail.com>
 <aMNrSgJt6_oRqh45@paquier.xyz>
 <CAN4RuQvQ3ATcYvfTR1LzJnUJXpo_F8mgz-+WxoZsyusLLmCwYA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="RCU+V4+gZW4drYqS"
Content-Disposition: inline
In-Reply-To: 
 <CAN4RuQvQ3ATcYvfTR1LzJnUJXpo_F8mgz-+WxoZsyusLLmCwYA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aMor2AHvdbZWTwCD%40paquier.xyz>
Precedence: bulk


--RCU+V4+gZW4drYqS
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Sep 16, 2025 at 02:45:03PM +0300, Kouber Saparev wrote:
> =D0=9D=D0=B0 =D0=BF=D1=82, 12.09.2025=E2=80=AF=D0=B3. =D0=B2 3:37 Michael=
 Paquier <michael@paquier.xyz> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0:
>> With which process has this cascading standby been created?
>> Does the workload of the primary involve a high consumption of OIDs
>> for relations, say many temporary tables?
>=20
> Yes, we have around 150 entries added and deleted per second in pg_class,
> and around 800 in pg_attribute. So something is actively creating and
> dropping tables all the time.

The number of relations would most likely matter, I doubt that
pg_attribute contributes a lot in the frictions.  One of the test
scenarios I am running is exactly that: a bunch of CREATE/DROP tables
running concurrently with backends in a cascading standby accessing
them, while tweaking the OID generated in the control file to force
wraparounds.  I am failing to re-create your problem, for now at
least.=20

> > Another thing that may help is the WAL record history.  Are you for
> > example seeing attempts to drop twice the same pgstats entry in WAL
> > records?  Perhaps the origin of the problem is in this area.  A
> > refcount of 2 is relevant, of course.
> >
>=20
> How could we dig into this, i.e. inspecting such attempts in the WAL
> records?

Yeah, with pg_walinspect or pg_waldump, but I doubt that you retain
enough WAL history to be able to ping at something specific.  One
pattern I am looking for is duplicated drops initiated from the WAL
records, when wraparound hits, or at least if there's a window small=20
enough that two WAL records are generated and attempt to generate the
same entry drop.  That's just an assumption at this stage.


> Is it enough to upgrade the replicas or we need to upgrade the primary as
> well?

That would be both.  There are always reasons to update to the latest
minor version of Postgres.  See the release notes for a bunch of them,
that's usually convincing enough especially with CVEs addressed.  :)
--
Michael

--RCU+V4+gZW4drYqS
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmjKK9gACgkQnvQgOdby
QH3uFxAAj7R/rDSgfAOgHtOFxY685x0Pb7Z0uAj4OQrbToZjn78uad0bq2va/ylf
CWeVLPM98+t2BPqcU4P/bBBVqwHr4dCkNuqFR8hvUdveKfBsPDOxtTzjbSF9evRG
XPj3JxAnkw9yB8iZp9DCsgyjw59U9wlFrzWFNfHRNR5OBjU/szfkuZeeEtSPZ+Xv
6wNzwYo1aVT7eqHEvYUScWP365s/OY7PDAKs4KFehLEFiJY8H938avyvcnR6ERMf
0PAmm82XmO6NutQWadSTL+VhOCawnM6yOVMP3i5pcZ/APsdl1yGiPEYCFaTJkOeb
Q/VjgC+YeqQCfW6KowHBEEDJEWPhY2Yg6XHCANmNsCCXbtvQveGLF1Huqdj5t4tY
H2I3aM76JlkignJxbnMrC2WyGtBExQ463l7hfMNs1JEE5lbdn9npRgNFxZ4wc/Eb
RwYxBM8TOu6ERIlkvNorbSza3I2SJXkJEPTuB8/+RyaxsWe/CsbYn1OSofpGtIfg
pq9HCrW4Oc2aFke7bJ8WxC9weLmMD6wrEbYm9zvPBFGYfn3WOKqy7Ncmz5GLUN0U
UNjYqWUVfmyoPG62pAxL8o6hnNvJwTJFZTXHQ7hw5pB/iVT2nfY9J2RfyuYkdmLG
Y/m2TZB2qgEb+2Tw10HQ9T1cenT9QeayJNq+tqghhAqr8EEszMI=
=knT1
-----END PGP SIGNATURE-----

--RCU+V4+gZW4drYqS--