|[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]|
On 08/10/2012 12:20 PM, Tom Lane wrote:
Craig Ringer <ringerc@xxxxxxxxxxxxx> writes:On 08/10/2012 10:06 AM, Tom Lane wrote:That sure sounds like the source of your problem. It might be sane if it killed only processes that *had been idle* for at least three seconds, but I'm not sure there is any easy way to determine that ...wouldn't:select * from pg_stat_activity where current_query = '<IDLE> in transaction' AND query_start < current_timestamp - INTERVAL '3 seconds';do it?No, that would find sessions that were idle and whose last command started at least 3 seconds ago. But it might have completed only microseconds ago. The symptoms Greg is describing are consistent with this kill script running during a short interval between his index-build command and his COMMIT.
Eh. For some reason I thought query_start was updated whenever current_query was set, so it'd be updated when the session went idle. Nonesensical in retrospect.
Does this approach look sane? Note idlers and then reap them if they haven't started new queries next pass:
CREATE UNLOGGED TABLE reap ( procpid integer, query_start timestamptz, added timestamptz ); -- Run every 20s or whatever to note idlers and when we noticed them insert into reap (procpid,query_start,added) select procpid, query_start, current_timestamp from pg_stat_activity where current_query = '<IDLE> in transaction'; -- Clean out old entries and kill idlers WITH r AS ( DELETE FROM reap r2 WHERE r2.added + INTERVAL '10 seconds' < current_timestamp RETURNING * ) SELECT r.procpid, pg_terminate_backend(r.procpid) FROM r INNER JOIN pg_stat_activity s ON (r.procpid = s.procpid AND r.query_start = s.query_start ) GROUP BY r.procpid;ie "where we've noted more than 1 minute ago that a pid was idle, and query_start hasn't changed so no new query has been run, kill the backend".
Of course, there's race where the backend could be starting a new query even as you kill it for idleness, but that's true in any idle-in-transaction control scheme (other than having a true transaction timeout feature *within* the backend).
-- Craig Ringer -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[Postgresql General] [Postgresql Performance] [Postgresql PHP] [Postgresql Jobs] [PHP Users] [PHP Databases] [PHP Home] [PHP on Windows] [Kernel Newbies] [PHP Classes] [PHP Databases] [Yosemite]