SET SESSION MEMORYCAP

Limits how much memory can be allocated to any request in the current session. This limit only applies to the current session; it does not limit the total amount of memory used by multiple sessions.

Syntax

SET SESSION MEMORYCAP limit

Parameters

limit

One of the following:

  • '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:

      MEMORYCAP '40%'

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

      MEMORYCAP '10G'

  • =DEFAULT: Sets the memory cap for queries in this session to the user's MEMORYCAP value. A new session is initially set to this value.
  • NONE: Removes the memory cap for this session.

Privileges

  • Superusers can increase session memory cap to any value.
  • Non-superusers can only set the session memory cap to a value equal to or lower than their own user setting.

Examples

Set the session memory cap to 2 gigabytes:

=> SET SESSION MEMORYCAP '2G';
SET
=> SHOW MEMORYCAP;
   name    | setting
-----------+---------
 memorycap | 2097152
(1 row)

Revert the memory cap to the default setting as specified in the user profile:

=> SET MEMORYCAP=DEFAULT;
SET
=> SHOW MEMORYCAP;
   name    | setting
-----------+---------
 memorycap | 2013336
(1 row)

See Also

Managing Workloads