Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 10 Jun 2024 15:37:24 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Floris Van Nee <florisvannee@optiver.com>
Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>,
	Andres Freund <andres@anarazel.de>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	"tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: error "can only drop stats once" brings down database
Message-ID: <ZmafJO3lpMjbyHXv@paquier.xyz>
References: <68430b1b76d44558b7b07ae8c69151f6@Optiver.com>
 <ZmE-qm8d9kfDfnMy@paquier.xyz>
 <ZmFRlwr/53/tXqxn@ip-10-97-1-34.eu-west-3.compute.internal>
 <20240607.101540.1972690976596948719.horikyota.ntt@gmail.com>
 <ZmJ0bBODm6AitiC3@paquier.xyz>
 <20240607031258.l4fcpvulo3uee5g6@awork3.anarazel.de>
 <ZmLTneAb+EaZMS9A@ip-10-97-1-34.eu-west-3.compute.internal>
 <ZmLXnAtRSUudsFqg@paquier.xyz>
 <2cb577e176774caca28c724898768a38@Optiver.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="kfQEn90Oto44/evi"
Content-Disposition: inline
In-Reply-To: <2cb577e176774caca28c724898768a38@Optiver.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZmafJO3lpMjbyHXv%40paquier.xyz>
Precedence: bulk


--kfQEn90Oto44/evi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Sat, Jun 08, 2024 at 11:52:43AM +0000, Floris Van Nee wrote:
> I've got an update about the bug. I managed to reproduce it locally
> after a lot of digging.
>
> How to repro:
> - Setup primary + replica
> - Open a psql session on both
> - On primary session: create table t (a int); select 't'::regclass::oid;
> - On replica session: select * from t;
> - On primary session: drop table t; vacuum pg_class; checkpoint;
> - Gdb attach to the backend for your primary, set a breakpoint for=20
> catalog.c:GetNewOidWithIndex, just before it calls GetNewObjectId()
> - On primary session: create table t (a int);
> - When it hits breakpoint, simulate oid wraparound by setting:
> ShmemVariableCache->nextOid =3D <the output value of the select earlier>
> This will make pg create the new table with the same oid as the previous =
one.
> - On primary session: drop table t; -- this triggers the replica to go do=
wn

Okay, this stuff makes the beginning of a week fun.

> The reason it crashes on replica is that the recovery process is responsi=
ble for dropping
> stats on commit, but it's not creating them on table creation. Thus, on t=
he second create
> table call, the old shared stats entry still exists (due to a backend sti=
ll have a ref to it),
> but it is never reinitialized by the logic in pgstat_reinit_entry(). On p=
rimary it's not possible
> to reach this state, because heap_create() creates the stats entry immedi=
ately when the
> table is created.
>
> I wonder what's the best way to fix this though.
> Should redo process call pgstat_create_relation somewhere, just like heap=
_create does?
> Should we just ignore this 'drop stats twice' error on standby?

Nah, ignoring the double-drop error does not seem right to me.
Wouldn't it make the most sense to ensure that the stats are dropped
on the standby instead on the first DROP replayed even if there are
still references to it hold, making sure that the stats entry with
this OID is gone before reusing it after wraparound?
--
Michael

--kfQEn90Oto44/evi
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmZmnyQACgkQnvQgOdby
QH2ybw/9GZrbx89xnSfxz2orvnq+DLK0z98d+hL+qq1C5vMMtyUx5WoscYYpIEJ9
yQ2AUUtqAJSE6B0lYjVCAFmRPWEUm7/xoVGeyjobrqbhjk84d9mYN+Bre+KOz0Ci
nldme1fXN8WIa34pWYZvl4HqvVqi0TTw0ntlA99uSGBlL8nU6ZMba5R0nBz00cXv
WfuHEHbCFiQwN6QJWPYXS3XuLrZd464Y53c85IFOFMwmyp/15rda2Kp/n+0miXYF
RDA7fL6NzI7FAe/JbMpMXj2EzrR9wC8WzAXWkj+E1TX940J7RF/g4hy2assZI+ga
+zzqnBQdKT3KLOXIQdTF+YcuJ3zcgaDhavP6MCN/synoyHk5o9d/3zyVmk/qvCwf
zgpSqWFQlZHyS7bmWxkkJpRFgjqtRvmHhg8GA5p5g2Jrt+AHkm55jbvqUhHcugcn
gHTkPashqjvHuGLoTlA6LEYku3SPDVK2t4SysXJaHKR7Q/mgy5DuNgMTHdeR5w7i
4MoTCCczb35/i4MMZNI3q7135L8tPtyC3e5HaugYCr8QRMky3cYUtt06XU6p6MD3
x1AH54BDqXXpvfecdj4JNlH6wkHZKpHrIqZSSf+GJ6Eunlfr9vQgkMd9DrsZA8ao
AA8TnertSkow1EMEfTdeqCKfpVWlxcGG4CnKcq/ka6Wu5Z0sp6E=
=jR0N
-----END PGP SIGNATURE-----

--kfQEn90Oto44/evi--