pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedweird long time query
9+ messages / 4 participants
[nested] [flat]
* weird long time query
@ 2019-12-17 02:58 Kaijiang Chen <chenkaijiang@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Kaijiang Chen @ 2019-12-17 02:58 UTC (permalink / raw)
To: pgsql-performance
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?
Best Wishes
Kaijiang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird long time query
@ 2019-12-17 11:08 Pavel Stehule <pavel.stehule@gmail.com>
parent: Kaijiang Chen <chenkaijiang@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2019-12-17 11:08 UTC (permalink / raw)
To: Kaijiang Chen <chenkaijiang@gmail.com>; +Cc: pgsql-performance
út 17. 12. 2019 v 11:45 odesílatel Kaijiang Chen <chenkaijiang@gmail.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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird long time query
@ 2019-12-17 17:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kaijiang Chen <chenkaijiang@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2019-12-17 17:04 UTC (permalink / raw)
To: Kaijiang Chen <chenkaijiang@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird long time query
@ 2019-12-18 03:23 Kaijiang Chen <chenkaijiang@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Kaijiang Chen @ 2019-12-18 03:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 <tgl@sss.pgh.pa.us> wrote:
> 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: weird long time query
@ 2019-12-18 03:25 Kaijiang Chen <chenkaijiang@gmail.com>
parent: Kaijiang Chen <chenkaijiang@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Kaijiang Chen @ 2019-12-18 03:25 UTC (permalink / raw)
To: Pg Bugs <pgsql-bugs@postgresql.org>
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.
Best Wishes
Kaijiang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: weird long time query
@ 2019-12-18 14:06 hubert depesz lubaczewski <depesz@depesz.com>
parent: Kaijiang Chen <chenkaijiang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: hubert depesz lubaczewski @ 2019-12-18 14:06 UTC (permalink / raw)
To: Kaijiang Chen <chenkaijiang@gmail.com>; +Cc: Pg Bugs <pgsql-bugs@postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: weird long time query
@ 2019-12-18 16:14 Kaijiang Chen <chenkaijiang@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kaijiang Chen @ 2019-12-18 16:14 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Thanks!
I learn the SQL from the web. pg views should be better.
BTW, I got the similar result (still see that proc) with "select * from
pg_stat_activity":
backend_start | 2019-11-25 16:27:05.103901+08
xact_start |
query_start | 2019-11-25 16:29:29.529318+08
state_change | 2019-11-25 16:29:29.529344+08
waiting | f
state | idle
backend_xid |
backend_xmin |
query | DEALLOCATE pdo_stmt_00000388
Looks not very nice :-)
On Wed, Dec 18, 2019 at 10:06 PM hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: weird long time query
@ 2019-12-18 16:23 hubert depesz lubaczewski <depesz@depesz.com>
parent: Kaijiang Chen <chenkaijiang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: hubert depesz lubaczewski @ 2019-12-18 16:23 UTC (permalink / raw)
To: Kaijiang Chen <chenkaijiang@gmail.com>; +Cc: Pg Bugs <pgsql-bugs@postgresql.org>
On Thu, Dec 19, 2019 at 12:14:26AM +0800, Kaijiang Chen wrote:
> Thanks!
> I learn the SQL from the web. pg views should be better.
> BTW, I got the similar result (still see that proc) with "select * from
> pg_stat_activity":
>
> backend_start | 2019-11-25 16:27:05.103901+08
> xact_start |
> query_start | 2019-11-25 16:29:29.529318+08
> state_change | 2019-11-25 16:29:29.529344+08
> waiting | f
> state | idle
> backend_xid |
> backend_xmin |
> query | DEALLOCATE pdo_stmt_00000388
>
> Looks not very nice :-)
not sure what you mean by not nice.
As you can clearly see the backend is *NOT* running anything (state is
idle).
Value in "query" column is simply last query that it ran. It *finished*
running this query at 2019-11-25 16:29:29.529344+08.
So your app is keeping connection open. It's not Pg problem or a bug.
Best regards,
depesz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: weird long time query
@ 2019-12-18 16:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2019-12-18 16:30 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Kaijiang Chen <chenkaijiang@gmail.com>; Pg Bugs <pgsql-bugs@postgresql.org>
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Thu, Dec 19, 2019 at 12:14:26AM +0800, Kaijiang Chen wrote:
>> BTW, I got the similar result (still see that proc) with "select * from
>> pg_stat_activity":
>> ...
>> state | idle
>> ...
>> query | DEALLOCATE pdo_stmt_00000388
>>
>> Looks not very nice :-)
> not sure what you mean by not nice.
That's a feature not a bug (and yes, the behavior is documented).
People requested that the view continue to display the last query
of an idle session. IIRC, the main argument was that otherwise
it's hard to tell apart a bunch of idle sessions.
If you don't like it, you can always do something like
case when state = idle then null else query end
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-12-18 16:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-17 02:58 weird long time query Kaijiang Chen <chenkaijiang@gmail.com>
2019-12-17 11:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2019-12-17 17:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-18 03:23 ` Kaijiang Chen <chenkaijiang@gmail.com>
2019-12-18 03:25 ` Kaijiang Chen <chenkaijiang@gmail.com>
2019-12-18 14:06 ` hubert depesz lubaczewski <depesz@depesz.com>
2019-12-18 16:14 ` Kaijiang Chen <chenkaijiang@gmail.com>
2019-12-18 16:23 ` hubert depesz lubaczewski <depesz@depesz.com>
2019-12-18 16:30 ` Tom Lane <tgl@sss.pgh.pa.us>
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