Arvados

Bug #18488

Updated by Ward Vandewege over 2 years 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: 

 <pre> 
  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) 
 </pre> 

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

Back