pg_stat_activity not showing current_query text

posted Feb 26, 2015, 9:08 AM by Sachchida Ojha
The gadget spec URL could not be found
pg_stat_activity table is most often used table to query the current activity of the database.
The gadget spec URL could not be found
sachi=# select * from pg_stat_activity;
 datid | datname | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start
         | client_addr | client_port | application_name |          xact_start           
-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------
---------+-------------+-------------+------------------+-------------------------------
 16993 | sachi   |   16717 |   10207 |       10 | gpadmin | <IDLE>                          | f       | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:56:30.2
74394-05 |             |          -1 | psql             | 
 16993 | sachi   |   32076 |   10740 |       10 | gpadmin | select * from pg_stat_activity; | f       | 2015-02-26 11:54:43.244429-05 | 2015-02-26 10:40:50.1
86253-05 |             |          -1 | psql             | 2015-02-26 11:54:43.244429-05
(2 rows)

when you run the same query from NON-Privileged ie non gpadmin user, you get something like 
The gadget spec URL could not be found
gpuser=# select * from pg_stat_activity;
 datid | datname | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start
         | client_addr | client_port | application_name |          xact_start           
-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------
---------+-------------+-------------+------------------+-------------------------------
 16993 | sachi   |   16717 |   10207 |       10 | gpadmin | <insuffient priviledge>    | f       | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:58:30.2
74394-05 |             |          -1 | psql             | 
 16993 | sachi   |   32076 |   10740 |       10 | gpadmin | <insuffient priviledge>    | f       | 2015-02-26 11:54:43.244429-05 | 2015-02-26 10:40:50.1
86253-05 |             |          -1 | psql             | 2015-02-26 11:58:43.244429-05
(2 rows)

How to solve this problem?
The gadget spec URL could not be found
Here is a simple way you can solve this. Ask your DBA who has gpadmin privilege to create a function like below and grant you the function privilege. Now you can query the function to get the desired result.
The gadget spec URL could not be found
gpadmin=# 
CREATE or REPLACE FUNCTION get_pg_stats() 
returns setof pg_stat_activity 
as 
'select * from pg_stat_activity;' 
LANGUAGE 'sql' 
SECURITY DEFINER; 

CREATE FUNCTION 

The gadget spec URL could not be found
 gpadmin=# GRANT ALL on FUNCTION get_pg_stats() to gpuser; 

GRANT
The gadget spec URL could not be found
gpuser=# select * from get_pg_stats();

 datid | datname | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start
         | client_addr | client_port | application_name |          xact_start           
-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------
---------+-------------+-------------+------------------+-------------------------------
 16993 | sachi   |   16717 |   10207 |       10 | gpadmin | <IDLE>                          | f       | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:56:30.2
74394-05 |             |          -1 | psql             | 
 16993 | sachi   |    2688 |   10742 |       10 | gpadmin | select * from pg_stat_activity; | f       | 2015-02-26 12:03:42.975227-05 | 2015-02-26 12:03:37.2
76689-05 |             |          -1 | psql             | 2015-02-26 12:03:42.975227-05
(2 rows)

sachi=# 

The gadget spec URL could not be found

The gadget spec URL could not be found

Comments