How can we find full query running in the database?
Note: You can see running queries in pg_stat_activity table. If the query is huge in length like having 1000 lines, we are not able to see full query from pg_stat_activity table .
1. using pg_stat_activity table
2. using log file created in pg_log directory
3. using gp_toolkit schema tables and views
Note: Amount of code you see depends on the client you use. In pgAdmin you can see only 1000 of them. You can dump the SQL using psql -c "copy (select * from pg_stat_activity) to stdout CSV header delimiter '|';" For example
[gpadmin@sachi ~]$ psql -c "copy (select * from pg_stat_activity) to stdout CSV header delimiter '|';"
16993|sachi|7245|5593|10|gpadmin|"copy (select * from pg_stat_activity) to stdout CSV header delimiter '|';"|f|2015-01-19 13:24:23.540755-05|2015-01-19 13:24:23.538773-05||-1|psql|2015-01-19 13:24:23.540755-05
If you still not able to see full text of the query, increase the parameter.
Sets the maximum length limit for the query text stored in current_query column of the system catalog view pg_stat_activity. The minimum length is 1024 characters.
Run this on master- gpconfig -c pgstat_track_activity_query_size -v 100001
This will increase the length of current_query column of pg_stat_activity table