From: Jerry Sievers <gsievers19@comcast.net>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Never kill -9 postgres client processes on Linux... but why not?
Date: Thu, 19 Apr 2018 08:59:48 -0400
Message-ID: <87po2vz70b.fsf@jsievers.enova.com> (raw)
In-Reply-To: <CAOC+FBVsc3ehFduq_z=5H6k2G-KyNCKnBzkzY0Q53nVpEasnhw@mail.gmail.com>
References: <CAOC+FBVsc3ehFduq_z=5H6k2G-KyNCKnBzkzY0Q53nVpEasnhw@mail.gmail.com>
Wells Oliver <wells.oliver@gmail.com> writes:
> 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.
Idle backends won't respond to a cancel since if they're "idle" there's
nothing to cancel :-)
If OTOH you tried pg_terminate_backend and this too drew no response,
then you might have a case of backend blocked in a critical section
such as SendV, symtomatic of a backend trying to write to a full pipe.
HTH
>
> 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?
>
> Thanks!
>
> --
> Wells Oliver
> wells.oliver@gmail.com
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, wells.oliver@gmail.com
Subject: Re: Never kill -9 postgres client processes on Linux... but why not?
In-Reply-To: <87po2vz70b.fsf@jsievers.enova.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox