Re: weird long time query
Kaijiang Chen <[email protected]> Wed, 18 Dec 2019 11:23:37 +0800
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CAAkGvS_wZ=Rk2MDzB4QT8=uNL52e=cMiDhDgA6U8FuMVrtSgJQ@mail.gmail.com> |
--00000000000012867c0599f1f99f Content-Type: text/plain; charset="UTF-8" I think I should also report it as a bug since logically, it couldn't exist. On Wed, Dec 18, 2019 at 1:04 AM Tom Lane <[email protected]> wrote: > Kaijiang Chen <[email protected]> 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 > --00000000000012867c0599f1f99f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">I think I should also report it as a bug since logically, = it couldn't exist.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr"= class=3D"gmail_attr">On Wed, Dec 18, 2019 at 1:04 AM Tom Lane <<a href= =3D"mailto:[email protected]">[email protected]</a>> wrote:<br></div><bl= ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef= t:1px solid rgb(204,204,204);padding-left:1ex">Kaijiang Chen <<a href=3D= "mailto:[email protected]" target=3D"_blank">[email protected]</a= >> writes:<br> > I'm using postgres 9.4.17 on centos 7.<br> > I check the running queries with the following SQL:<br> > SELECT<br> >=C2=A0 =C2=A0 =C2=A0procpid,<br> >=C2=A0 =C2=A0 =C2=A0start,<br> >=C2=A0 =C2=A0 =C2=A0now() - start AS lap,<br> >=C2=A0 =C2=A0 =C2=A0current_query<br> > FROM<br> >=C2=A0 =C2=A0 =C2=A0(SELECT<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0backendid,<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pg_stat_get_backend_pid(S.backendid) = AS procpid,<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pg_stat_get_backend_activity_start(S.= backendid) AS start,<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pg_stat_get_backend_activity(S.backen= did) AS current_query<br> >=C2=A0 =C2=A0 =C2=A0FROM<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(SELECT pg_stat_get_backend_idset() A= S backendid) AS S<br> >=C2=A0 =C2=A0 =C2=A0) AS S<br> > WHERE<br> >=C2=A0 =C2=A0 =C2=A0current_query <> '<IDLE>'<br> > ORDER BY<br> >=C2=A0 =C2=A0 =C2=A0lap DESC;<br> <br> Don't know where you got this query from, but it's wrong for any PG= <br> version more recent than (I think) 9.1.=C2=A0 We don't use "<ID= LE>" as an<br> indicator of idle sessions anymore; rather, those can be identified<br> by having state =3D 'idle'.=C2=A0 What's in the query column fo= r such a session<br> is its last query.<br> <br> > Then, I found a SQL that has run for some days (and still running):<br= > > procpid=C2=A0 =C2=A0 =C2=A0 =C2=A0| 32638<br> > start=C2=A0 =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 =C2=A0| 21 days 18:24:54.707369<= br> > current_query | DEALLOCATE pdo_stmt_00000388<br> <br> It's not running.=C2=A0 That was the last query it ran, back in Novembe= r :-(<br> You could zap the session with pg_terminate_backend(), but<br> pg_cancel_backend() is not going to have any effect because there's<br> no active query.<br> <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane<br> </blockquote></div> --00000000000012867c0599f1f99f--