What do I do if the "ERROR:temporary file size exceeds temp_file_limit(8388608kb)" error message appears when I execute an SQL statement on my ApsaraDB RDS for PostgreSQL instance? - ApsaraDB RDS

Problem description

The following error message appears when I execute an SQL statement on my ApsaraDB RDS for PostgreSQL instance:

ERROR: temporary file size exceeds temp_file_limit(8388608kb)

Causes

When you execute an SQL statement, a large temporary table is generated, and the storage that is occupied by temporary tables exceeds the upper limit that is specified by the temp_file_limit parameter.

Solutions

Query the upper limit of the storage that can be occupied by temporary tables and increase the upper limit.

Log on to your RDS instance by using Data Management (DMS). For more information, see Use DMS to log on to an ApsaraDB RDS for PostgreSQL instance.
In the left-side navigation pane of the DMS console, double-click the required database. The SQLConsole tab appears.
On the SQLConsole tab, enter the following statement and click Execute to query the upper limit of the storage that can be occupied by temporary tables:
```
show temp_file_limit;
```
On the SQLConsole tab, enter the following statement and click Execute to increase the upper limit of the storage that can be occupied by temporary tables:
```
alter role all set temp_file_limit = [$Temp_File_Limit];
```
Note
[$Temp_File_Limit] specifies the new upper limit of the storage that can be occupied by temporary tables. Unit: KB. The value of this parameter must be greater than the size of the temporary table that is mentioned in the error message. For example, if the size of the temporary table that is mentioned in the error message is 8388608kb, we recommend that you set this parameter to 20000000kb.
Close the SQLConsole tab and click the SQL Console tab. On the SQLConsole tab, re-execute the following statement to confirm that the new upper limit of the storage that can be occupied the temporary tables is applied:
```
show temp_file_limit;
```
Re-execute the SQL statement to verify that the error message does not appear.
Note
If the SQL statement is executed to perform a temporary query, we recommend that you restore the upper limit to the original value after the SQL statement is executed. If you do not restore the upper limit to the original value, temporary tables may exhaust the storage of your RDS instance, and your RDS instance may be locked.

Application scope

ApsaraDB RDS for PostgreSQL