Andy
Andy

Reputation: 8652

why do connections remain in pg_stat_activity after a client process has been killed?

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

Answers (0)

Related Questions