Re: Idle query that's not ""?

Hi

In the documentation describes the data in this field:

https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW  

"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