View SQL statements of all underlying nodes on the master node in greenplum

 

Greenplum is a distributed database with many PostgreSQL databases under it. Sometimes we need to know what the underlying node is doing. Can we directly have a view or SQL on the master node, you can view the SQL statements of each node and identify the machine and database of the port. The following describes the method:

The architecture of 3.3 and 4.0 has changed, so the method is different.

1. Create the v_active_ SQL view to view the SQL:

Create view v_active_ SQL

Select province, province, pg_stat_activity.usename, pg_stat_activity.waiting as W, to_char (Region, 'Mm-dd hh24: MI: ss': Text) as query_start, to_char (now ()-example, 'hh24: Mi ': Text) as exec, pg_stat_activity.current_query

From pg_stat_activity

Where pg_stat_activity.current_query <> '<idle>': Text

Order by pg_stat_activity.datname, to_char (pg_stat_activity.query_start, 'yyyymmdd hh24: MI: ss': text );

Gp3.3:

Create or replace function public. hostname (a text)

Returns text

As $

Import socket

Return socket. gethostname ()

$ Language plpythonu;

Query the SQL statements of each node:

Select hostname (current_query), current_setting (replace ('Port' | current_query, current_query, "), * From gp_dist_random ('v _ active_ SQL ') where current_query not like '% idle % ';

Gp4.0:

1. Create a hostname to get the hostname

Create or replace function public. hostname ()

Returns text

As $

Import socket

Return socket. gethostname ()

$ Language plpythonu;

2. Create the v_ SQL View:

Create view v_ SQL as select hostname () as hostname, current_setting ('Port') as port, * From v_active_ SQL;

3. Create the get_ SQL function:

Create or replace function get_ SQL (a text)

Returns setof v_ SQL

Security definer

As

$

Return plpy.exe cute ("select hostname () as hostname, current_setting ('Port') as port, response, response, pg_stat_activity.usename, pg_stat_activity.waiting as W, to_char (pg_stat_activity.query_start, 'Mm-dd hh24: MI: ss': Text) as query_start, to_char (now ()-pg_stat_activity.query_start, 'hh24: Mi ': Text) as exec, pg_stat_activity.current_query/

From pg_stat_activity/

Where pg_stat_activity.current_query <> '<idle>': text/

Order by pg_stat_activity.datname, to_char (pg_stat_activity.query_start, 'yyyymmdd hh24: MI: ss': Text )");

$ Language plpythonu;

4.

Create view all_seg_ SQL as select (T. get_ SQL). * from (select get_ SQL (gpname: Text) from gp_dist_random ('gp _ id') T;

The final effect is as follows:

Aligputf8 = # select * From all_seg_ SQL;

Hostname | Port | procpid | sess_id | usename | w | query_start | exec | current_query

---- + --- +-+ ------ + --- + ----------

Hadoop4 | 30000 | 14101 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

Hadoop4 | 30001 | 14103 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

Hadoop5 | 30000 | 10822 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

Hadoop5 | 30001 | 10824 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

Hadoop6 | 30000 | 16492 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

Hadoop6 | 30001 | 16494 | 84 | gpadmin | f | 03-31 18:44:16 | select * From all_seg_ SQL;

(6 rows)