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

Unfortunately, after more testing, it turns out this issue still
persists in PostgreSQL 14.4.

I have now encountered exactly the same problem as described in the
original issue below: One autovacuum session that never finishes with no
wait event (or is just incredibly slow and not finishing after many
hours although it should in minutes considering the relative small
dataset and normal operation), and the "client backend" and "parallel
worker" stuck on the same wait events as listed below  with the same
"SELECT COUNT (*)" SQL statement.

One thing to note as well, besides this being workstation level hardware
with ECC RAM, is that I now also activated 'pg_checksums' on the
PostgreSQL databases, and reloaded all data, so all data should now have
checksums.  No PostgreSQL error at all is generated via the ODBC
connection I use to access and update the database when this happens and
PostgreSQL appears stuck on the autovacuum. So I guess this now means I
can now pretty much exclude a hardware error, and this must be some
software issue, considering the checksums.

Marco

-------- Doorgestuurd bericht --------
Onderwerp: Re: "SELECT COUNT(*) FROM" still causing issues (deadlock)
in PostgreSQL 14.3?
Datum: Fri, 1 Jul 2022 11:49:47 +0200
Van: Marco Boeringa <marco(at)boeringa(dot)demon(dot)nl>
Aan: pgsql-bugs(at)lists(dot)postgresql(dot)org

FWIIW, I have not been able to reproduce this issue in the latest 14.4
release up until now, with quite significant testing. So it seems this
issue is finally fixed, although only time will tell for sure, as the
issue was intermittent.

Marco

-------- Doorgestuurd bericht --------
Onderwerp: "SELECT COUNT(*) FROM" still causing issues (deadlock) in
PostgreSQL 14.3?
Datum: Thu, 16 Jun 2022 08:10:12 +0200
Van: Marco Boeringa <marco(at)boeringa(dot)demon(dot)nl>
Aan: pgsql-bugs(at)lists(dot)postgresql(dot)org

Hi,

Anyone else encountering this?

I have had issues with SELECT COUNT (*) in PG 14.2, that I think were
supposed to be fixed in PG 14.3.

However, I have now seen PostgreSQL locking up with a SELECT COUNT (*)
statement in PG 14.3 twice.

Note that in both cases, the code that issued this statement, had
successfully processed similar statements against other tables multiple
times before locking up.

When I look in pgAdmin, I see three "active" sessions, that never finish
though (or are extremely slow...), and I do see some minor "Tuple out"
activity sporadically (a few thousand tuples at a time):

- autovacuum: "VACUUM <TABLE_NAME>" with no Wait Event

- client backend: "SELECT COUNT (*) FROM <TABLE_NAME>" with Wait Event
"IPC:ExecuteGather"

- parallel worker: "SELECT COUNT (*) FROM <TABLE_NAME>" with Wait Event
"LWLock:BufferContent"

When this happens, it is impossible to stop the sessions, when I choose
"Cancel Query" or "Terminate Session", pgAdmin returns "success" for the
operation, yet the sessions remain visible in the pgAdmin window, even
after clicking "refresh", something that definitely doesn't happen in
ordinary situations.

Under "Locks" in pgAdmin, I see an "AccesShareLock" for the "client
backend" and "parallel worker", and a "ShareUpdateExclusiveLock" for the
"autovacuum" for the table involved.

Additionally, when this happens, my "File system root" of Ubuntu slowly
starts filling up, until 100% full (growth from +/-630GB to 1TB), and
PostgreSQL shuts down:

"server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request."

Note that the "logging collector" is set "off", and no logs are thus
being kept (this is a test system, and I don't care much for the logs).

In order to get out this situation, I have needed to restore from backup.

My system:

- Windows Hyper-V virtualized Ubuntu 20.04.1 LTS

- PG 14.3 (Ubuntu 14.3-1.pgdg20.04+1)

- POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="140"
GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1"
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

All run on an HPZ840 system with 256 GB ECC RAM, dual Xeon E5-2680v4,
Windows 10. 5x2TB NVMe configured as Windows "Storage Space" attached
via a PCIe Express card.

Data: OpenStreetMap data for the entire Planet, +/- 1.4 TB database if
processing finishes.

Marco