Postgres Pro
Downloads
Home   >   mailing lists

Thread: Idle query that's not ""?

Idle query that's not ""?

From
Ron
Date:
Hi,

v9.6.6

Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text "<IDLE>"?

postgres=# select pid,
           xact_start as txn_start,
           to_char(EXTRACT(epoch FROM now() - query_start), '999,999.0000') as query_age_secs,
           state,
           cast (query as char(20))
from pg_stat_activity
where datname <> 'postgres'
order by query_start;
;
 
 pid  |           txn_start           | query_age_secs | state  |        query       
------+-------------------------------+----------------+--------+----------------------
26538 | 2018-11-06 14:40:55.053842-05 |    3,451.9853  | active | SELECT  to_char(b.pr
27497 | 2018-11-06 14:59:26.946854-05 |    2,340.5871  | active | SELECT  to_char(b.pr
29110 | 2018-11-06 14:59:50.479934-05 |    2,317.1725  | active | SELECT  to_char(b.pr
 8357 |                               |    1,324.1356  | idle   | SELECT  CAST(c.ecid
 9016 | 2018-11-06 15:34:51.535476-05 |      215.8391  | active | SELECT  to_char(b.pr
 9810 | 2018-11-06 15:35:00.483292-05 |      206.7676  | active | SELECT  to_char(b.pr
11260 |                               |      190.0814  | idle   | WITH configs AS (SEL
12800 | 2018-11-06 15:35:49.540631-05 |      157.9880  | active | SELECT  to_char(b.pr
11355 |                               |       42.9772  | idle   | SELECT CASE WHEN typ
22618 | 2018-11-06 15:38:02.317146-05 |       25.3219  | active | SELECT  to_char(b.pr
23176 | 2018-11-06 15:38:12.90985-05  |       14.7325  | active | SELECT  to_char(b.pr
23566 | 2018-11-06 15:38:28.802919-05 |         .6116  | active | select tms.TRIGGER.T
23588 | 2018-11-06 15:38:29.207373-05 |         .2089  | active | select cds.IMAGE_RPS
23590 | 2018-11-06 15:38:29.233724-05 |         .1814  | active | select tms.TRIGGER.T
23584 | 2018-11-06 15:38:29.046393-05 |         .0442  | active | select tms.MARK_SENS
23595 | 2018-11-06 15:38:29.403969-05 |         .0001  | active | select JOB_STEP.JOB_
(16 rows)


--
Angular momentum makes the world go 'round.

Re: Idle query that's not ""?

From
Hellmuth Vargas
Date:
Hi
In the documentation describes the data in this field:

"Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed. "


El mar., 6 de nov. de 2018 a la(s) 15:46, Ron (ronljohnsonjr@gmail.com) escribió:
Hi,

v9.6.6

Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text "<IDLE>"?

postgres=# select pid,
           xact_start as txn_start,
           to_char(EXTRACT(epoch FROM now() - query_start), '999,999.0000') as query_age_secs,
           state,
           cast (query as char(20))
from pg_stat_activity
where datname <> 'postgres'
order by query_start;
;
 
 pid  |           txn_start           | query_age_secs | state  |        query       
------+-------------------------------+----------------+--------+----------------------
26538 | 2018-11-06 14:40:55.053842-05 |    3,451.9853  | active | SELECT  to_char(b.pr
27497 | 2018-11-06 14:59:26.946854-05 |    2,340.5871  | active | SELECT  to_char(b.pr
29110 | 2018-11-06 14:59:50.479934-05 |    2,317.1725  | active | SELECT  to_char(b.pr
 8357 |                               |    1,324.1356  | idle   | SELECT  CAST(c.ecid
 9016 | 2018-11-06 15:34:51.535476-05 |      215.8391  | active | SELECT  to_char(b.pr
 9810 | 2018-11-06 15:35:00.483292-05 |      206.7676  | active | SELECT  to_char(b.pr
11260 |                               |      190.0814  | idle   | WITH configs AS (SEL
12800 | 2018-11-06 15:35:49.540631-05 |      157.9880  | active | SELECT  to_char(b.pr
11355 |                               |       42.9772  | idle   | SELECT CASE WHEN typ
22618 | 2018-11-06 15:38:02.317146-05 |       25.3219  | active | SELECT  to_char(b.pr
23176 | 2018-11-06 15:38:12.90985-05  |       14.7325  | active | SELECT  to_char(b.pr
23566 | 2018-11-06 15:38:28.802919-05 |         .6116  | active | select tms.TRIGGER.T
23588 | 2018-11-06 15:38:29.207373-05 |         .2089  | active | select cds.IMAGE_RPS
23590 | 2018-11-06 15:38:29.233724-05 |         .1814  | active | select tms.TRIGGER.T
23584 | 2018-11-06 15:38:29.046393-05 |         .0442  | active | select tms.MARK_SENS
23595 | 2018-11-06 15:38:29.403969-05 |         .0001  | active | select JOB_STEP.JOB_
(16 rows)


--
Angular momentum makes the world go 'round.


--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet 
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate

Re: Idle query that's not ""?

From
"David G. Johnston"
Date:
On Tue, Nov 6, 2018 at 1:46 PM Ron <ronljohnsonjr@gmail.com> wrote:
> Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text
"<IDLE>"?

Because having the text "<IDLE>" is redundant with the field status=idle

David J.

Re: Idle query that's not ""?

From
Ron
Date:
Right.  But when does the query text become "<IDLE>"?  Or has that become obsolete? (We recently migrated from 8.4.)

On 11/06/2018 02:53 PM, Hellmuth Vargas wrote:
Hi
In the documentation describes the data in this field:

"Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed. "


El mar., 6 de nov. de 2018 a la(s) 15:46, Ron (ronljohnsonjr@gmail.com) escribió:
Hi,

v9.6.6

Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text "<IDLE>"?

postgres=# select pid,
           xact_start as txn_start,
           to_char(EXTRACT(epoch FROM now() - query_start), '999,999.0000') as query_age_secs,
           state,
           cast (query as char(20))
from pg_stat_activity
where datname <> 'postgres'
order by query_start;
;
 
 pid  |           txn_start           | query_age_secs | state  |        query       
------+-------------------------------+----------------+--------+----------------------
26538 | 2018-11-06 14:40:55.053842-05 |    3,451.9853  | active | SELECT  to_char(b.pr
27497 | 2018-11-06 14:59:26.946854-05 |    2,340.5871  | active | SELECT  to_char(b.pr
29110 | 2018-11-06 14:59:50.479934-05 |    2,317.1725  | active | SELECT  to_char(b.pr
 8357 |                               |    1,324.1356  | idle   | SELECT  CAST(c.ecid
 9016 | 2018-11-06 15:34:51.535476-05 |      215.8391  | active | SELECT  to_char(b.pr
 9810 | 2018-11-06 15:35:00.483292-05 |      206.7676  | active | SELECT  to_char(b.pr
11260 |                               |      190.0814  | idle   | WITH configs AS (SEL
12800 | 2018-11-06 15:35:49.540631-05 |      157.9880  | active | SELECT  to_char(b.pr
11355 |                               |       42.9772  | idle   | SELECT CASE WHEN typ
22618 | 2018-11-06 15:38:02.317146-05 |       25.3219  | active | SELECT  to_char(b.pr
23176 | 2018-11-06 15:38:12.90985-05  |       14.7325  | active | SELECT  to_char(b.pr
23566 | 2018-11-06 15:38:28.802919-05 |         .6116  | active | select tms.TRIGGER.T
23588 | 2018-11-06 15:38:29.207373-05 |         .2089  | active | select cds.IMAGE_RPS
23590 | 2018-11-06 15:38:29.233724-05 |         .1814  | active | select tms.TRIGGER.T
23584 | 2018-11-06 15:38:29.046393-05 |         .0442  | active | select tms.MARK_SENS
23595 | 2018-11-06 15:38:29.403969-05 |         .0001  | active | select JOB_STEP.JOB_
(16 rows)


--
Angular momentum makes the world go 'round.


--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet 
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate


--
Angular momentum makes the world go 'round.

Re: Idle query that's not ""?

From
"David G. Johnston"
Date:
On Tue, Nov 6, 2018 at 1:59 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
> Right.  But when does the query text become "<IDLE>"?  Or has that become obsolete? (We recently migrated from 8.4.)

That behavior changed sometime around 9.0; since it always shows the
last query executed it logically follows that it will never show the
placeholder "<IDLE>" (I suppose it might do so upon initial connect if
no queries have been sent yet...not sure what it says then or even if
it is possible)

David J.

Re: Idle query that's not ""?

From
Ron
Date:
On 11/06/2018 03:04 PM, David G. Johnston wrote:
> On Tue, Nov 6, 2018 at 1:59 PM Ron <ronljohnsonjr@gmail.com> wrote:
>> Right.  But when does the query text become "<IDLE>"?  Or has that become obsolete? (We recently migrated from
8.4.)
> That behavior changed sometime around 9.0; since it always shows the
> last query executed it logically follows that it will never show the
> placeholder "<IDLE>" (I suppose it might do so upon initial connect if
> no queries have been sent yet...not sure what it says then or even if
> it is possible)

So... obsolete.  Thanks.

-- 
Angular momentum makes the world go 'round.