cte_max_memory_bytes

cte_max_memory_bytes limits memory retained by recursive CTE batches for one query on each CN.

Description

cte_max_memory_bytes is a dynamic system variable with both GLOBAL and SESSION scope. When the projected memory for recursive CTE batches exceeds the session value on a CN, the query is aborted with a recursive-CTE memory-quota error. The value is an approximate OOM circuit breaker, not byte-exact billing for every operator in the statement.

The default is 1073741824 bytes (1 GiB). The valid range is 0 through 1099511627776 bytes (1 TiB). Set the value to 0 to disable this circuit breaker. Use cte_max_recursion_depth to limit the number of recursive iterations, and see WITH (Common Table Expressions) for the recursive-CTE rules.

Syntax

SET [GLOBAL | SESSION] cte_max_memory_bytes = number_of_bytes

The variable can be changed dynamically. SESSION is the default scope when no scope keyword is specified.

Arguments

cte_max_memory_bytes accepts a non-negative integer number of bytes from 0 to 1099511627776. 0 disables the limit; the default is 1073741824 (1 GiB).

Examples

SET SESSION cte_max_memory_bytes = 16777216;
SELECT @@session.cte_max_memory_bytes;
SET SESSION cte_max_memory_bytes = 0;
SELECT @@session.cte_max_memory_bytes;