-
Title
How to Change the Batch Text Limit for PostgreSQL PI Agents in Foglight -
Description
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.
-
Cause
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.
-
Resolution
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
The information in the script provided is known to work successfully; however, it has not been officially tested by Quest Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Support and Development.Quest Support and Development recommend always making a backup of the current Foglight installation and database prior to execution of any scripts that may modify it.How to view the current limit for all PostgreSQL PI agents**You can view the limits 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 (list_Postgresql_PI_paecResolverBatchTextLimit_sizes_for_all_agents.groovy)
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.Notes- Users must be logged into Supportlink to download this script.
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.
-
Defect ID
FOGGENERAL-I-147 -
Attachments