Setting the Max Memory Available by Query

Jim_Knicely

Vertica 9.1.1 introduces the new Resource Pool parameter MAXQUERYMEMORYSIZE. Its value represents the maximum amount of memory the pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.

Example:

**dbadmin=> CREATE USER test;
CREATE USER

dbadmin=> CREATE RESOURCE POOL limited_by_query MAXQUERYMEMORYSIZE '1K';
CREATE RESOURCE POOL

dbadmin=> GRANT USAGE ON RESOURCE POOL limited_by_query TO test;
GRANT PRIVILEGE

dbadmin=> GRANT USAGE ON SCHEMA public TO test;
GRANT PRIVILEGE

dbadmin=> GRANT SELECT ON public.big_table TO test;
GRANT PRIVILEGE

dbadmin=> \c - test
You are now connected as user "test".

dbadmin=> SHOW resource_pool;
     name      |     setting
---------------+------------------
resource_pool | limited_by_query
(1 row)

dbadmin=> SELECT * FROM public.big_table;
ERROR 8722:  The minimal memory required by the query [9224 KB] exceeds the query cap size [1 KB]
HINT:  Increase MAXQUERYMEMORYSIZE parameter of the 'limited_by_query' resource pool**