Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 16 Jun 2023 10:58:38 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Will Mortensen <will@extrahop.com>
Cc: Andres Freund <andres@anarazel.de>, pgsql-bugs@lists.postgresql.org,
	Jacob Speidel <jacob@extrahop.com>
Subject: Re: BUG #17973: Reinit of pgstats entry for dropped DB can break
 autovacuum daemon
Message-ID: <ZIvBzm0DwRo60Qwk@paquier.xyz>
References: <17973-bca1f7d5c14f601e@postgresql.org>
 <20230614213724.gweo666oxqrugm44@awork3.anarazel.de>
 <20230614215744.ddeuii7jcojbp3yv@awork3.anarazel.de>
 <20230614223012.cfyzxyfnt3ggqkt4@awork3.anarazel.de>
 <ZIu0bR6qSArdm6bw@paquier.xyz>
 <CAMpnoC4Tfyp2-JDE7+Yf-KFd0PgT3xZeezM6XjGA3=jfAFz6RA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="7q0qDqfH2O9te2Gs"
Content-Disposition: inline
In-Reply-To: 
 <CAMpnoC4Tfyp2-JDE7+Yf-KFd0PgT3xZeezM6XjGA3=jfAFz6RA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZIvBzm0DwRo60Qwk%40paquier.xyz>
Precedence: bulk


--7q0qDqfH2O9te2Gs
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Jun 15, 2023 at 06:36:41PM -0700, Will Mortensen wrote:
> On Thu, Jun 15, 2023 at 6:01=E2=80=AFPM Michael Paquier <michael@paquier.=
xyz> wrote:
>> It requires a few manual steps, but I have been able to stuck the
>> autovacuum launcher schedule.  Nice investigation from the reporters.
>>
>> I may be missing something here, but finishing with an inconsistent
>> database list (generated based on the pgstat database entries) in the
>> autovacuum launcher is not something that can happen only because of a
>> worker, right?  A normal backend would call pgstat_update_dbstats()
>> once it exists, re-creating a fresh entry with the dropped database
>> OID.  Is that right?
>=20
> Yes, sorry, Jacob was able to repro with a normal backend just now. We
> probably should have tried that earlier. :-)

Okay, thanks for confirming.  Yes, I was able to stuck the scheduler
for both.  FWIW, I have initially hardcoded a stop point with an
on-disk file around InitPostgres() before we take the shared lock.
But thinking harder, it is possible to be sneaky with the following
steps to get these incorrect stat entries:
1) LOCK pg_database in a first session, on a database different than
the one to drop.
2) Connect with a second session to the database to drop, stuck
because of the previous LOCK when scanning pg_database to find the
tuple OID, during connection startup, just before taking the shared
lock.
3) Third session executing DROP DATABASE, with a session that
connected before the pg_database lock.
4) Commit transaction of first session to release pg_database lock,
the database is dropped and the second session fails to connect with
the database renamed.

> I'm also unsure if reiniting the pgstats entry (as opposed to creating
> a new one) is actually necessary or just what we happened to observe.
> We're definitely not very familiar with these internals. :-)

The proposed patch does better than that, actually, and takes the
approach of not touching the stats for the database dropped/renamed
by delaying MyDatabaseId which is what the backend uses to update the
database-level pgstat entries.
--
Michael

--7q0qDqfH2O9te2Gs
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmSLwc4ACgkQnvQgOdby
QH14YQ//dtpbXWJo6nFCUme/gjsex1m4E+3tDxI66YKXKUPS4crKIFPzSxylkUFU
9YskllHdIBv/QUK58aNPiyteWJTS37yXBsjgcbBjNd/8poXL4Tj8Glv13sO4GtHX
sTCI128uMYHEMod+caWZW+72bPyFna9pmuHCfARRJve4otUPyKFO16JnLpPsnyJC
CaRkEAeg1GseM3iWn4SN4IDKkDqHpjTJtKHEojjNKOUFXGGMHN+ejxIbhCNsPZ6S
TQjlZIe0Mv9nnPIY+LB16eL5TzwK20qkLRBQg6DZ73JH/dnW8trwmLWu/0ZBxWqE
g8C9/HIxyqGKWF3IiQovjrnCKBCfLoV8E5WrzDOXbAw1lGRKwgJ6rwrKLH8mt2U3
/6WVrQFZEWrLAsWbtG3MIC+Sb8wGKaGLBfaBvYzdtTuoVgR37OJVg55Z5/mFlyQa
dafXaPo3i4pfEMfb+0Y5hzuzGoBwroLlTsgpsL+4L0Baf5ViuWZezJFk77deO6kg
9BGlUlP3JN8auJxV9LHbEUUa1+Q60WswFsHzsgYfnyBqAKEY8cAxAfqvppa+tTXN
gVRPJxeY6eH/G0WkOVw1r8LecQB9qQHNUMQVHl8s8YujVGvleSw/ZwCD06B4v7Uv
GgqADzNz1WnBVL+CB/YchEjw1995AhWNlU1dJX87/jfhV9sXKMo=
=akTa
-----END PGP SIGNATURE-----

--7q0qDqfH2O9te2Gs--