How to Change the Batch Text Limit for PostgreSQL PI Agents in Foglight (4379631)

SQL Statements displayed in the PostgreSQL PI agent are displayed with the message "SQL text is out of the collected batch configured size".

This article explains the `paecResolverBatchTextLimit` property for PostgreSQL Performance Investigator (PI) agents in Foglight, including what it does, its default, and how to safely change it using a script.

What is `paecResolverBatchTextLimit`?

The `paecResolverBatchTextLimit` property controls the maximum number of characters collected for SQL statements by the PostgreSQL PI agent in each batch. If a SQL statement exceeds this limit, it will be truncated in Foglight’s monitoring and reporting.

The default limit is 16,300 characters and will appear -2147483648 (default value) when the PostgreSQL agent is first created. 

Why should this be adjusted?

• To capture longer SQL statements in their entirety for diagnostics or auditing.
•  To reduce storage or memory usage (by lowering the limit).
• To ensure compliance with environment-specific requirements.

Workaround

How to Change the Limit

**You can change the limit for all PostgreSQL PI agents or for selected agents using a Groovy script in the Foglight Script Console.**

1. Open the Script Console
Navigate to Administration > Tooling > Script Console in the Foglight web UI.

2. Use the attached groovy script 
Copy and paste the following script into the Script Console: Users must be logged into Supportlink to download this script.

3. Run the Script
Press Execute in the Script Console and then review the output for success or any errors.

4. Deactivate the PostgreSQL PI agent in the Agent Status dashboard

5. 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 

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

Notes

• Adjust `listAgentNames` to target specific agents, or leave as `["all"]` to update all.
• Choose a value for `newBatchTextLimit` as needed for your environment.
• For large environments, consider making changes during a maintenance window.
• This script is only relevant for Foglight premise systems. Contact Support to request the value to be increased in Foglight Cloud.
• Users must be logged into Supportlink to download this script.

Disclaimer

Status

Enhancement ID FOGGENERAL-I-147 has been logged to add a UI field to increase the batch text size in each of the types of PI agents similar to SQL Server PI.