Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4? - Mailing list pgsql-bugs
| From | Marco Boeringa |
|---|---|
| Subject | Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4? |
| Date | |
| Msg-id | 791d1850-f337-8914-1ba0-512f4e0fef26@boeringa.demon.nl Whole thread Raw |
| In response to | Re: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3? (Marco Boeringa <marco@boeringa.demon.nl>) |
| Responses |
Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?
|
| List | pgsql-bugs |
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@boeringa.demon.nl> |
| Aan: | pgsql-bugs@lists.postgresql.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@boeringa.demon.nl> |
| Aan: | pgsql-bugs@lists.postgresql.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
pgsql-bugs by date: