Re: Idle query that's not "<IDLE>"?

От:

Ron <ronljohnsonjr@gmail.com>

Дата:

6 ноября 2018 г. в 21:11:10

On 11/06/2018 03:04 PM, David G. Johnston wrote:
> On Tue, Nov 6, 2018 at 1:59 PM Ron  wrote:
>> Right.  But when does the query text become ""?  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 "" (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.

Idle query that's not "<IDLE>"?

От:

Ron <ronljohnsonjr@gmail.com>

Дата:

6 ноября 2018 г. в 20:46:20

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 "<IDLE>"?

От:

Ron <ronljohnsonjr@gmail.com>

Дата:

6 ноября 2018 г. в 20:59:34

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:

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

--
Angular momentum makes the world go 'round.

Re: Idle query that's not "<IDLE>"?

От:

"David G. Johnston" <david.g.johnston@gmail.com>

Дата:

6 ноября 2018 г. в 21:04:23

On Tue, Nov 6, 2018 at 1:59 PM Ron  wrote:
>
> Right.  But when does the query text become ""?  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 "" (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 "<IDLE>"?

От:

"David G. Johnston" <david.g.johnston@gmail.com>

Дата:

6 ноября 2018 г. в 20:53:37

On Tue, Nov 6, 2018 at 1:46 PM Ron  wrote:
> Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text ""?

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

David J.

Re: Idle query that's not "<IDLE>"?

От:

Hellmuth Vargas <hivs77@gmail.com>

Дата:

6 ноября 2018 г. в 20:53:22

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