Hi,
On 19/04/18 16:40, Wells Oliver wrote:
Had an issue tonight where I had a bunch of stalled queries from a
client connection and I just... could... not... kill... them. We
disconnected the client machine, turned it off, picked it up, shook it
around, yelled at it, and still these idle queries remained in
pg_stat_activity.
Then I did select pg_cancel_backend(pid) from pg_stat_activity where
client_addr = '..' and they just would... not... go.. away.
So me being the big smart system administrator guy with shell access,
I logged in, and did a kill -9 xxx where xxx was the sme pid from the
pg_stat_activity result and... they finally went away!
Felt good about myself until I realized, well, so did every other
connection, and in fact PG momentarily went into recovery mode.
Everything was fine, but a) why is it a bad idea to kill -9 a client
PG process, but pg_cancel_backend() is OK-- and b) what to do about
stalled PG queries that won't die when you disconnect AND when you
pg_cancel_backend() them?
Did you try pg_terminate_backend? I'm guessing that might not have
worked either...but is worth it trying before belting them with kill -9!
Kill -9 leaves the shared memory the backend was using intact but
possibly corrupted (partial writes etc), so Postgres shuts down
everything! This is pretty 'normal' in the db world (I think DB2 does
the same if you kill -9 a connection from the OS).
regards
Mark
