How to increase the SQL Statement length in the PostgreSQL and PostgreSQL PI agents (4379746)

After enabling pg_stat_statements in the PostgreSQL database only 1000 or 1024 character length of the SQL text can be seen in the PostgreSQL statements dashboard and within the PostgreSQL PI  "View SQL Text" expandable panel.

• The 1000 or 1024 character SQL text size reflects the inclusion of tabs and special characters. Depending on the text editor used to count characters, sizes may vary (e.g. a tab may be converted by a text editor to 4-5 spaces)

The PostgreSQL and PostgreSQL PI agent SQL Statement length may be limited by different settings. 

In the PostgreSQL agent Statements dashboard

To increase the PostgreSQL agent SQL Statement text length, adjust the Statement Text Length field in the PostgreSQL agent's Agent Status Properties page under the Statement Tracking session. After adjusting this value from the default of 1000 characters, deactivate and reactivate the PostgreSQL agent. The SQL Statement should reflect the updated value once the agent runs the collection again.

In the PostgreSQL PI agent SQL PI dashboard

1). Adjust the track_activity_query_size parameter in the PostrgreSQL pg_config file. 

• This value specifies the amount of memory reserved to store the text of the currently executing command for each active session, for the pg_stat_activity.query field. If this value is specified without units, it is taken as bytes. The default value is 1024 bytes. This parameter can only be set at server start.

2). Deactivate the PostgreSQL PI agent in the Agent Status dashboard

3). Truncate the spipg_syntax_dim_1m_{timestamp} tables it the PostgreSQL PI repository database.

• The purpose if this is the clear out recently collected data from PI so that the full SQL text can be collected by the agent 

4). Activate the PostgreSQL PI agent and then wait a few minutes for the SQL statements to collect and display in the SQL PI dashboard

Note: Refer to knowledgebase article 4379631 if SQL Statements are displayed in the PostgreSQL PI agent with the message "SQL text is out of the collected batch configured size".