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