How to change the current_query/query display content length in pg_stat_activity view

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to change the current_query/query display content-length in pg_stat_activity view.

In Greenplum 5.x and below the column is named "current_query"

In Greenplum 6.x and above the column is named "query"

Environment

Resolution

After GPDB v4.2.61.0, Pivotal introduced a new parameter to control the length of current_query column in pg_stat_activity view. This is very useful when trying to look at the complete content of big queries. 

In GPDB 5.x and earlier versions the configuration parameter is pgstat_track_activity_query_size

In GPDB 6.x and later versions the configuration parameter is track_activity_query_size

This parameter sets the maximum length for the query text stored in the current_query / query column of the system catalog view pg_stat_activity. The minimum and default length is 1024 characters.

Value Range	Default	Set Classifications
integer	1024	local system restart

A database restart is required for this change to take effect. An example of the procedure is displayed below: 

gpadmin=# show pgstat_track_activity_query_size ;
 pgstat_track_activity_query_size 
----------------------------------
 1024
(1 row)
gpadmin=# \q

[gpadmin@gpdb-sandbox ~]$ gpconfig -c pgstat_track_activity_query_size -v 2048 
20101008:19:58:52:014569 gpconfig:gpdb-sandbox:gpadmin-[INFO]:-completed successfully with parameters '-c pgstat_track_activity_query_size -v 2048'

[gpadmin@gpdb-sandbox ~]$ gpstop -afr

[gpadmin@gpdb-sandbox ~]$ psql 
Timing is on.
psql (8.3.23)
Type "help" for help.

gpadmin=# show pgstat_track_activity_query_size ;
 pgstat_track_activity_query_size 
----------------------------------
 2048
(1 row)

Additional Information