SET SESSION TEMPSPACECAP

Sets the maximum amount of temporary file storage space that any request issued by the session can consume. If a query's execution plan requires more storage space than the session TEMPSPACECAP, it returns an error.

Syntax

SET SESSION TEMPSPACECAP limit

Parameters

limit

Sets how much memory can be allocated for temporary space to the current session, where limit is specified in this format:

  • NONE (default):  Unlimited temporary storage space
  • = DEFAULT: Session TEMPSPACECAP is set to the user's TEMPSPACECAP value.

  • 'max‑expression': A string value that specifies the memory limit, one of the following:

    • int% — Expresses the maximum as a percentage of total memory available to the Resource Manager, where int is an integer value between 0 and 100.For example:

      SET SESSION TEMPSPACECAP '40%';

    • int{K|M|G|T} — Expresses memory allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      SET SESSION TEMPSPACECAP'10G;

Privileges

Nonsuperusers:

  • Restricted to setting only their own sessions
  • Session TEMPSPACECAP cannot be greater than their own TEMPSPACECAP.

Examples

Set the session TEMPSPACECAP to 20 gigabytes:

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

SHOW displays the TEMPSPACECAP in kilobytes.

Set the session TEMPSPACECAP to unlimited:

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

See Also