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

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