From: Floris Van Nee <florisvannee@Optiver.com>
To: Andres Freund <andres@anarazel.de>
CC: Tom Lane <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+AIAsEUZQ
Date: Sun, 2 Jun 2024 22:01:53 +0000
Message-ID: <47ca6fcc687e408287871f7eaaaf3c3b@Optiver.com>
References: <b14ae28029f64757bb64613be2549a74@Optiver.com>
 <215498.1708454947@sss.pgh.pa.us>
 <8e5cb534706a4f43bd74405c8fd91437@Optiver.com>
 <20240505160915.6boysum4f34siqct@awork3.anarazel.de>
 <20240505223546.6yvjzgqifuoiii3e@awork3.anarazel.de>
In-Reply-To: <20240505223546.6yvjzgqifuoiii3e@awork3.anarazel.de>
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/47ca6fcc687e408287871f7eaaaf3c3b%40Optiver.com>
Precedence: bulk

> I suspect that the "high value" oids are related to the issue. I bet you'=
re
> running into OID wraparound for objects at a much higher frequency than
> most users.  I suspect the reason this causes problems is the problem I j=
ust
> found and described here:

I definitely suspect oid wraparound has something to do with it. However, g=
iven what I found on the other thread ( https://www.postgresql.org/message-=
id/flat/17947-b9554521ad963c9c%40postgresql.org  ), I don't think the two h=
ave the exact same root cause. The other thread seems a clear case of 'forg=
ot to call gc' when it should.

>=20
> The fact that we're not triggering "stats object garbage collection" for =
most
> drops also explains why you're much more likely to see this on a standby
> than on a primary. An a primary it's going to be rare to have a single ba=
ckend
> live long enough to observe an oid wraparound leading to one backend
> accessing stats for the same object type with the same oid after that obj=
ect
> previously having been dropped. But most stats accesses on a standby are
> going to be by the same process, the startup process.  You'd still need s=
ome
> other accesses to prevent the object from being dropped "immediately", bu=
t
> that could be due to shorter lived processes.
>=20

As mentioned on other thread, I do think garbage collection gets called as =
part of pgstat_execute_transactional_drops in recovery.

There's still the case of the "skip gc of entry if pending" though, however=
 I struggle to understand how this can lead to such an error in the recover=
y process after wraparound. I'd expect the gc to happen relatively frequent=
ly (at least much more frequently than a wraparound) as tables get created/=
dropped quite frequently. Next to that, because the recovery process is the=
 only one creating/dropping relations on standby, it should be impossible t=
o get in a state where the recovery "local" stats cache has a non-gc'd entr=
y that should be dropped? Because when dropping, at least it removes the lo=
cal cache entry (even if it cannot remove the shared one). So later, when c=
reating it again in pgstat_get_entry_ref, it should always hit the pgstat_r=
einit_entry path?

Would it make sense to at least commit your patch to enhance the error mess=
age a bit?


-Floris