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