Getting database exception (temporary file size exceeds temp_file_limit (1048576kB)) while running report definition
Hello Team,
I have a requirement where more than 100 columns (but would be less than 150) need to be extracted from the database. Some of these columns are as aggregate properties of different classes, so I have used 4-5 different declare indexes as class join in this report definition. I'm getting the below exception on standalone running it. "An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: 53400 Message: ERROR: temporary file size exceeds temp_filesyskB) DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: temporary file size exceeds temp_file_limit (1048576kB) | SQL Code: 0 | SQL State: 53400 " If anyone has ever faced a similar issue or has any idea to resolve this issue then please let me know. I Thanks in advance. Regards, Parvesh Kumar
***Edited by Moderator Marije to change type from Pega Academy to Product***
Accepted Solution
Updated: 5 Aug 2022 10:01 EDT
Pegasystems Inc.
US
RE: org.postgresql.util.PSQLException: ERROR: temporary file size exceeds temp_file_limit (1048576kB)
It seems that your postgresql server has config setting temp_file_limit set to 1048576kB
You can change the temp_file_limit setting to -1 in the servers' pg config file postgresql.conf
More info is available from PostgreSQL: Documentation: 11: 19.4. Resource Consumption
temp_file_limit(integer)-
Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. The value is specified in kilobytes, and
-1(the default) means no limit. Only superusers can change this setting.
BlueRose Technologies Pvt. Ltd.
IN
@PhilipShannon Thank you so much for taking out time and giving your suggestions. It really helps a lot :)
Pegasystems Inc.
AU
Setting temp_file_limit to -1 is not recommended. It can cause any un-optimized query to use unlimited temporary space and consume all available space in the database. Once database space is 100% utilized, database will not be accessible and this can bring down the system. Please note that, this limit is per postgresql process. Each process can consume the temporary space up to the limit specified by temp_file_limit.
In case you are encountering the error due to temp_file_limit, review the query in question. Optimizing the query by adding appropriate indexes and additional conditions to restrict the data that is retrieved can resolve the error.