Re: weird long time query

--000000000000805dc10599e45b56
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=C3=BAt 17. 12. 2019 v 11:45 odes=C3=ADlatel Kaijiang Chen <chenkaijiang@gm=
ail.com>
napsal:

> 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;
>

I think so this query is weird - probably this query was finished

you should to use constraint

WHERE state <> 'idle';

Regards

Pavel


> 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?
>
> Best Wishes
> Kaijiang
>
>

--000000000000805dc10599e45b56
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">=C3=BAt 17. 12. 2019 v=C2=A011:45 ode=
s=C3=ADlatel Kaijiang Chen &lt;<a href=3D"mailto:[email protected]">ch=
[email protected]</a>&gt; napsal:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr">I&#39;m using postgres=C2=A09.4.17 on=
 centos 7.<div>I check the running queries with the following SQL:<div>SELE=
CT <br>=C2=A0 =C2=A0 procpid, =C2=A0<br>=C2=A0 =C2=A0 start, =C2=A0<br>=C2=
=A0 =C2=A0 now() - start AS lap, =C2=A0<br>=C2=A0 =C2=A0 current_query =C2=
=A0<br>FROM <br>=C2=A0 =C2=A0 (SELECT <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 backe=
ndid, =C2=A0<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_stat_get_backend_pid(S.backe=
ndid) AS procpid, =C2=A0<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_stat_get_backend=
_activity_start(S.backendid) AS start, =C2=A0<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 pg_stat_get_backend_activity(S.backendid) AS current_query =C2=A0<br>=
=C2=A0 =C2=A0 FROM <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (SELECT pg_stat_get_back=
end_idset() AS backendid) AS S =C2=A0<br>=C2=A0 =C2=A0 ) AS S =C2=A0<br>WHE=
RE <br>=C2=A0 =C2=A0 current_query &lt;&gt; &#39;&lt;IDLE&gt;&#39; <br>ORDE=
R BY <br>=C2=A0 =C2=A0 lap DESC;<br></div></div></div></blockquote><div><br=
></div><div>I think so this query is weird - probably this query was finish=
ed <br></div><div><br></div><div>you should to use constraint <br></div><di=
v><br></div><div>WHERE state &lt;&gt; &#39;idle&#39;;</div><div><br></div><=
div>Regards</div><div><br></div><div>Pavel<br></div><div><br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><div></div>=
<div><br></div><div>Then, I found a SQL that has run for some days (and sti=
ll running):</div><div>procpid =C2=A0 =C2=A0 =C2=A0 | 32638<br>start =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 | 2019-11-25 16:29:29.529318+08<br>lap =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 | 21 days 18:24:54.707369<br>current_query | DEALLOCAT=
E pdo_stmt_00000388<br></div><div><br></div><div>I tried to kill it with:=
=C2=A0SELECT pg_cancel_backend(32638) but it takes no effects.</div><div><b=
r></div><div>What&#39;s this query and what shall I do for it?</div><div><b=
r></div><div>Best Wishes</div><div>Kaijiang</div><div><br></div></div></div=
>
</blockquote></div></div>

--000000000000805dc10599e45b56--