From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Marco Boeringa <marco@boeringa.demon.nl>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?
Date: Mon, 25 Jul 2022 15:39:11 +0200
Message-ID: <bf3f10c1-c811-6d8d-8ff3-213fe0516cc9@enterprisedb.com> (raw)
In-Reply-To: <f6175d33-0e5d-480a-6e08-560ec4d25a0e@boeringa.demon.nl>
References: <85acb61d-4cbf-7544-c734-429993257547@boeringa.demon.nl>
<791d1850-f337-8914-1ba0-512f4e0fef26@boeringa.demon.nl>
<Ytr1cTgWLHhk40DB@momjian.us>
<2d7ff154-d0db-502d-aca8-a605fafcf395@boeringa.demon.nl>
<6c9bf4fd-f22b-4894-d7c9-30bf68ffcf6d@boeringa.demon.nl>
<883945.1658590435@sss.pgh.pa.us>
<2dbccee7-9616-997d-cf19-fa23e29e0497@boeringa.demon.nl>
<f6175d33-0e5d-480a-6e08-560ec4d25a0e@boeringa.demon.nl>
On 7/25/22 08:04, Marco Boeringa wrote:
> To extend on this, two interesting questions that come to mind are:
>
> - Does running SELECT COUNT(*) create WAL?
>
Yes. An obvious example is updating the visibility map (which is always
logged to WAL) or hint bits (which may be WAL logged). I'd also bet we
may generate WAL for indexes, e.g. to kill deleted tuples.
> - Is it potentially conceivable that there is a kind of cross-database
> vulnerability *within one and the same PostgreSQL cluster*, where an
> issue in one database causes the WAL in another database to no longer
> successfully be written to disk during checkpoints? I have never seen
> processing errors where PostgreSQL emitted true PostgreSQL errors with
> error numbers cause issues like that and affect a second database in the
> same cluster, but since no error is generated here, and there might be
> some uncatched error, I wonder?
>
> I am especially asking the second question since, although I wrote there
> is no edit activity going on potentially generating WAL in the affected
> small database, which is true, there *was* processing on Planet sized
> data going on in a second database in the same cluster. That certainly
> *is* capable of generating 890GB of WAL if nothing is cleaned up during
> checkpoints due to checkpoints failing.
>
WAL is a resource shared by all the databases in the cluster, so if that
gets broken it's broken for everyone.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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: tomas.vondra@enterprisedb.com, marco@boeringa.demon.nl, tgl@sss.pgh.pa.us, bruce@momjian.us, pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?
In-Reply-To: <bf3f10c1-c811-6d8d-8ff3-213fe0516cc9@enterprisedb.com>
* 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