pg_stat_activity not showing current_query text

posted Apr 28, 2017, 8:10 AM by Sachchida Ojha
pg_stat_activity table is most often used table to query the current activity of the database.
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 
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?
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.
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 
 gpadmin=# GRANT ALL on FUNCTION get_pg_stats() to gpuser; 

GRANT
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=# 
Comments