Re: Fwd: weird long time query

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