Reputation: 8652
I'm debugging a "sorry, too many clients already" issue with Postgres and just learned about the pg_stat_activity
table. I noticed there are a lot of dangling idle connections in it for nonexistent PIDs, here are just two examples:
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+--------------+-------+----------+----------+-----------------------------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
32998 | clarity_test | 4700 | 10 | postgres | | 172.19.0.1 | | 65056 | 2020-12-26 23:09:44.000108+00 | | 2020-12-26 23:09:47.41226+00 | 2020-12-26 23:09:47.439006+00 | Client | ClientRead | idle | | | UPDATE "MQTTDevices" SET "numFailedPasswordsTried"=0 WHERE "id" = 6 RETURNING * | client backend
32998 | clarity_test | 4704 | 10 | postgres | | 172.19.0.1 | | 65114 | 2020-12-26 23:09:45.435574+00 | | 2020-12-26 23:09:45.496313+00 | 2020-12-26 23:09:45.496469+00 | Client | ClientRead | idle | | | UNLISTEN "MQTTDeviceChannels/deviceId/4"
In all likelihood I killed the test processes that created these connections with Ctrl-C.
I must fundamentally misunderstand something because I would have expected Postgres to find out that the sockets got broken, and close those connections, rolling back transactions if necessary. As far as I read in https://dba.stackexchange.com/questions/81408/is-a-postgres-long-running-query-aborted-if-the-connection-is-lost-broken, when I terminated those processes my OS should be sending RST on the TCP sockets and Postgres should get it.
I'm running Postgres via Docker for Mac, I don't suppose that could somehow interfere with this?
I wouldn't think it should make a difference, but I'm using node-pg
, some connections via pooling, others via individual client instances (e.g. for LISTEN
).
Upvotes: 1
Views: 572