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