From: Michael Paquier <michael@paquier.xyz>
To: Will Mortensen <will@extrahop.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: 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
Date: Fri, 16 Jun 2023 10:58:38 +0900
Message-ID: <ZIvBzm0DwRo60Qwk@paquier.xyz> (raw)
In-Reply-To: <CAMpnoC4Tfyp2-JDE7+Yf-KFd0PgT3xZeezM6XjGA3=jfAFz6RA@mail.gmail.com>
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>
On Thu, Jun 15, 2023 at 06:36:41PM -0700, Will Mortensen wrote:
> On Thu, Jun 15, 2023 at 6:01 PM 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?
>
> 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
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: michael@paquier.xyz, will@extrahop.com, andres@anarazel.de, jacob@extrahop.com
Subject: Re: BUG #17973: Reinit of pgstats entry for dropped DB can break autovacuum daemon
In-Reply-To: <ZIvBzm0DwRo60Qwk@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox