From: Floris Van Nee <florisvannee@Optiver.com>
To: Michael Paquier <michael@paquier.xyz>,
        Bertrand Drouvot
	<bertranddrouvot.pg@gmail.com>
CC: 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>
Thread-Topic: [External] Re: error "can only drop stats once" brings down
 database [External]
Thread-Index: 
 AQHaZC2BJX3cZ68Gf0SnR3ukCGL5orGGQZEwgALhs4CAAGv+AIAsEUZQgAHuzgCAAg6I8IABEKoAgAAWkICAAUFxAIAAGUIAgAAHhACAAGn2gIAABMQAgAHQRtA=
Date: Sat, 8 Jun 2024 11:52:43 +0000
Message-ID: <2cb577e176774caca28c724898768a38@Optiver.com>
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>
In-Reply-To: <ZmLXnAtRSUudsFqg@paquier.xyz>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: RE: error "can only drop stats once" brings down database
Archived-At: 
 <https://www.postgresql.org/message-id/2cb577e176774caca28c724898768a38%40Optiver.com>
Precedence: bulk

> Okay, I've just applied the elog() version down to 15 then.  Now, about t=
he
> entry getting dropped twice..

Thanks Michael.

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 on=
e.
- On primary session: drop table t; -- this triggers the replica to go down

The reason it crashes on replica is that the recovery process is responsibl=
e for dropping
stats on commit, but it's not creating them on table creation. Thus, on the=
 second create
table call, the old shared stats entry still exists (due to a backend still=
 have a ref to it),
but it is never reinitialized by the logic in pgstat_reinit_entry(). On pri=
mary it's not possible
to reach this state, because heap_create() creates the stats entry immediat=
ely 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_c=
reate does?
Should we just ignore this 'drop stats twice' error on standby?

-Floris