Bug #18488

Updated by Ward Vandewege 6 months ago

This is bad because systemd restarts controller and after a number of tries, postgresql postgres (?) runs out of database connections.

Basically; it seems that postgresql does not kill waiting lock requests when the connection that the request came in on is severed abruptly.

This easy to replicate with two simultaneous psql connections (e.g. `select pg_advisory_lock(1);`). Remove the one that is waiting on the lock with `kill -9` (so it does not clean up the connection). Then check in the other one:

SELECT pid, age(clock_timestamp(), query_start), state, usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state != 'idle'
ORDER BY query_start desc;
pid | age | state | usename | query
3924760 | 00:25:23.843543 | active | postgres | select pg_advisory_lock(1);
(1 row)

Presumably we could switch to the 'try' variant and retry occasionnaly to avoid this problem.