Обсуждение: 'show full processlist' in postgres?

Hallo pgsql-sql@postgresql.org,

I'm looking for a way to display the active querys of postgres. The 
mysql way is a 'show [full] processlist'.

After I've read http://pgsqld.active-venture.com/monitoring-stats.html I 
set STATS_COMMAND_STRING, STATS_BLOCK_LEVEL, STATS_ROW_LEVEL and 
STATS_START_COLLECTOR to 'true'.

Then I executed the query from the bottom of the page:

> SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
>    pg_stat_get_backend_activity(S.backendid) AS current_query
>    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;

The result was:
procpid | current_query
---------+---------------   1367 |  27387 |    930 |  28425 | <IDLE>
(4 rows)

Why there isn't any query in the 'current_query' row? Calling the query 
multiple times just changes the PID (sometimes) but 'current_query' was 
always empty. How do I show up the a list of querys that will be 
processed right in this moment?

P.S: I'm using postgresql 7.4.7 and I was logged I as 'postgres'.

-- 
So long... Fuzz

did you restart postgresql and use the pg_stat_activity view instead (just to save some typing).

Jim



---------- Original Message -----------
From: Erik Wasser <erik.wasser@iquer.net>
To: pgsql-sql@postgresql.org
Sent: Fri, 1 Jul 2005 15:58:46 +0200
Subject: [SQL] 'show full processlist' in postgres?

> Hallo pgsql-sql@postgresql.org,
> 
> I'm looking for a way to display the active querys of postgres. The 
> mysql way is a 'show [full] processlist'.
> 
> After I've read http://pgsqld.active-venture.com/monitoring-stats.html I 
> set STATS_COMMAND_STRING, STATS_BLOCK_LEVEL, STATS_ROW_LEVEL and 
> STATS_START_COLLECTOR to 'true'.
> 
> Then I executed the query from the bottom of the page:
> 
> > SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
> >    pg_stat_get_backend_activity(S.backendid) AS current_query
> >    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;
> 
> The result was:
> 
>  procpid | current_query
> ---------+---------------
>     1367 |
>    27387 |
>      930 |
>    28425 | <IDLE>
> (4 rows)
> 
> Why there isn't any query in the 'current_query' row? Calling the query 
> multiple times just changes the PID (sometimes) but 'current_query' was 
> always empty. How do I show up the a list of querys that will be 
> processed right in this moment?
> 
> P.S: I'm using postgresql 7.4.7 and I was logged I as 'postgres'.
> 
> -- 
> So long... Fuzz
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
------- End of Original Message -------

On Friday 01 July 2005 18:24, Jim Buttafuoco wrote:

> did you restart postgresql and use the pg_stat_activity view instead
> (just to save some typing).

No. Is a restart necessary or will a 'reload' do also the trick?

I've done the restart:

postgres=#  SELECT * from pg_stat_activity ; datid  |  datname  | procpid | usesysid |  usename  | current_query | 
query_start
---------+-----------+---------+----------+-----------+---------------+-------------7349453 | admintool |    5436 |
100 | admintool |               |7349454 | postgres  |    5437 |        1 | postgres  |               |
 
(2 rows)

But 'current_query' is still always empty... B-(

-- 
So long... Fuzz

Erik Wasser <erik.wasser@iquer.net> writes:
> But 'current_query' is still always empty... B-(

The pg_stats views lag reality by a certain amount, so checking for your
own query is generally not gonna work.  Try starting a long-running
query in another session.
        regards, tom lane

am  01.07.2005, um 19:13:36 +0200 mailte Erik Wasser folgendes:
> postgres=#  SELECT * from pg_stat_activity ;
>   datid  |  datname  | procpid | usesysid |  usename  | current_query | 
> query_start
> ---------+-----------+---------+----------+-----------+---------------+-------------
>  7349453 | admintool |    5436 |      100 | admintool |               |
>  7349454 | postgres  |    5437 |        1 | postgres  |               |
> (2 rows)
> 
> But 'current_query' is still always empty... B-(

enable stats_command_string in your postgresql.conf


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

On Friday 01 July 2005 22:19, Andreas Kretschmer wrote:

> enable stats_command_string in your postgresql.conf

That was missing. Thanks.

-- 
So long... Fuzz