SET SESSION TEMPSPACECAP

Sets the maximum amount of temporary file storage 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

Arguments

limit

The maximum amount of temporary file storage to allocate to the current session, one of the following:

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

  • String that specifies the storage limit, one of the following:

    • int% expresses the maximum as a percentage of total temporary storage 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 storage allocation in kilobytes, megabytes, gigabytes, or terabytes. For example:

      SET SESSION TEMPSPACECAP '10G';

Privileges

Non-superusers:

  • 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