Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?

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