SET SESSION TEMPSPACECAP

Sets the maximum amount of temporary file storage space that any request issued by the session can consume.

Syntax

SET SESSION TEMPSPACECAP 'space-limit' | = default | NONE

Parameters

'space-limit'

The maximum amount of temporary file space the session can use. To set a limit, use a numeric value followed by a unit (for example: '10G'). The unit can be one of the following:

% percentage of total temporary storage space available. (In this case, the numeric value must be 0-100).
K—Kilobytes
M—Megabytes
G—Gigabytes
T—Terabytes

Setting this value to = default sets the session's TEMPSPACECAP to the user's TEMPSPACECAP value.

Setting this value to NONE results in the session having unlimited temporary storage space. This is the default value.

Privileges

This command requires superuser privileges to increase the TEMPSPACECAP over the user's TEMPSPACECAP limit.
Regular users can change the TEMPSPACECAP associated with their own sessions to any value less than or equal to their own TEMPSPACECAP. They cannot increase its value beyond their own TEMPSPACECAP value.

Notes

This limit is per session, not per user. A user could open multiple sessions, each of which could use up to the TEMPSPACECAP.
Any execution plan that exceeds its TEMPSPACECAP usage results in the error:

ERROR: Exceeded temp space cap.

Examples

The following command sets a TEMPSPACECAP of 20gigabytes on the session:

=> SET SESSION TEMPSPACECAP '20G';
SET
=> SHOW TEMPSPACECAP;
     name     | setting
--------------+----------
 tempspacecap | 20971520
(1 row)

Note: SHOW displays the TEMPSPACECAP in kilobytes.

To return the memorycap to the previous setting:

=> SET SESSION TEMPSPACECAP NONE;
SET
=> SHOW TEMPSPACECAP;
     name     |  setting
--------------+-----------
 tempspacecap | UNLIMITED
(1 row)