From: hubert depesz lubaczewski <depesz@depesz.com>
To: Kaijiang Chen <chenkaijiang@gmail.com>
Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Subject: Re: Fwd: weird long time query
Date: Wed, 18 Dec 2019 15:06:31 +0100
Message-ID: <20191218140631.GA1083@depesz.com> (raw)
In-Reply-To: <CAAkGvS_FSZf=uzTwWbTnRe7hXruga_opSeCPDueNR09v9Ow47Q@mail.gmail.com>
References: <CAAkGvS8ocfeVFOCsxoAY6suzMkrW+O9twRAQ2c4WrZNbQ=vSpg@mail.gmail.com>
<CAAkGvS_FSZf=uzTwWbTnRe7hXruga_opSeCPDueNR09v9Ow47Q@mail.gmail.com>
On Wed, Dec 18, 2019 at 11:25:32AM +0800, Kaijiang Chen wrote:
> 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;
>
> 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
>
> I tried to kill it with: SELECT pg_cancel_backend(32638) but it takes no
> effects.
>
> What's this query and what shall I do for it?
>
> I think it is a bug since logically, this query should be gone.
It's not a bug. Most likely this backend is not doing anything.
You're using old way to check if backend is working - current_query <>
'<IDLE>';
Check: select * from pg_stat_activity where pid = 32638
Most likely you'll see state = 'idle'
In such cases, query just shows last executed query, not currently
running one.
Also - WHY are you calling internal pg* functions directly, instead of
using pg_stat_activity view?
Best regards,
depesz
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: depesz@depesz.com, chenkaijiang@gmail.com, pgsql-bugs@postgresql.org
Subject: Re: Fwd: weird long time query
In-Reply-To: <20191218140631.GA1083@depesz.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