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.
-------- Doorgestuurd bericht --------
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
