From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kaijiang Chen <chenkaijiang@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: weird long time query
Date: Tue, 17 Dec 2019 12:04:20 -0500
Message-ID: <5019.1576602260@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
References: <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
Kaijiang Chen <chenkaijiang@gmail.com> writes:
> I'm using postgres 9.4.17 on centos 7.
> I check the running queries with the following SQL:
> SELECT
> procpid,
> start,
> now() - start AS lap,
> current_query
> FROM
> (SELECT
> backendid,
> pg_stat_get_backend_pid(S.backendid) AS procpid,
> pg_stat_get_backend_activity_start(S.backendid) AS start,
> pg_stat_get_backend_activity(S.backendid) AS current_query
> FROM
> (SELECT pg_stat_get_backend_idset() AS backendid) AS S
> ) AS S
> WHERE
> current_query <> '<IDLE>'
> ORDER BY
> lap DESC;
Don't know where you got this query from, but it's wrong for any PG
version more recent than (I think) 9.1. We don't use "<IDLE>" as an
indicator of idle sessions anymore; rather, those can be identified
by having state = 'idle'. What's in the query column for such a session
is its last query.
> Then, I found a SQL that has run for some days (and still running):
> procpid | 32638
> start | 2019-11-25 16:29:29.529318+08
> lap | 21 days 18:24:54.707369
> current_query | DEALLOCATE pdo_stmt_00000388
It's not running. That was the last query it ran, back in November :-(
You could zap the session with pg_terminate_backend(), but
pg_cancel_backend() is not going to have any effect because there's
no active query.
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chenkaijiang@gmail.com
Subject: Re: weird long time query
In-Reply-To: <5019.1576602260@sss.pgh.pa.us>
* 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