What happens if I revoke access to general pool for an users ?

davidvilla

I have an user which is by default configured to a dedicated high concurrency resource pool. Sometimes request has borrowed memory from general pool as well(GENERAL_MEMORY_BORROWED_KB).

Need to understand what happens if I revoke access to general pool for this user? Would it still continue to borrow memory from general pool?

Jim_Knicely

Hi,

Even if you revoke the USAGE privilege on the GENERAL POOL, the "dedicated high concurrency resource pool" can still barrow memory from the GP.

Example:

dbadmin=> CREATE USER some_user;
CREATE USER

dbadmin=> CREATE RESOURCE POOL high_concurreny_pool;
CREATE RESOURCE POOL

dbadmin=> ALTER RESOURCE POOL high_concurreny_pool MEMORYSIZE '1K' PLANNEDCONCURRENCY 32;
ALTER RESOURCE POOL

dbadmin=> GRANT USAGE ON RESOURCE POOL high_concurreny_pool TO some_user;
GRANT PRIVILEGE

dbadmin=> GRANT SELECT ON big_varchar_table TO some_user;
GRANT PRIVILEGE

dbadmin=> REVOKE USAGE ON RESOURCE POOL general FROM some_user;
REVOKE PRIVILEGE

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

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

dbadmin=> PROFILE SELECT COUNT(*) FROM public.big_varchar_table;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996277124325 and statement_id=2;
ERROR 8186:  User lacks privileges on resource pool 'general'
HINT:  Please contact the administrator to receive privileges on the assigned pool or switch to a pool you already have privileges on, using SET SESSION RESOURCE POOL <poolname>

dbadmin=> SET SESSION RESOURCE POOL high_concurreny_pool;
SET

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

dbadmin=> PROFILE SELECT COUNT(*) FROM public.big_varchar_table;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996277124325 and statement_id=5;
NOTICE 3557:  Initiator memory for query: [on pool high_concurreny_pool: 6916 KB, minimum: 6916 KB]
NOTICE 5077:  Total memory required by query: [6916 KB]
   COUNT
------------
 1000000000
(1 row)

Note that the query needed 7 KB to run, but the high_concurreny_pool resource pool only has 1 KB, so it borrowed 6 KB from the GP.

Jim_Knicely

If you do not want the "dedicated high concurrency resource pool" to borrow from the GP, set its MAXMEMORYSIZE to be the same as its MEMORYSIZE.

Example:

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

dbadmin=> ALTER RESOURCE POOL high_concurreny_pool MAXMEMORYSIZE '1K';
ALTER RESOURCE POOL

dbadmin=> SELECT memorysize, maxmemorysize FROM  resource_pools WHERE name = 'high_concurreny_pool';
 memorysize | maxmemorysize
------------+---------------
 1K         | 1K
(1 row)

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

dbadmin=> SET SESSION RESOURCE POOL high_concurreny_pool;
SET

dbadmin=> PROFILE SELECT COUNT(*) FROM public.big_varchar_table;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996277124381 and statement_id=2;
NOTICE 3557:  Initiator memory for query: [on pool high_concurreny_pool: 6916 KB, minimum: 6916 KB]
ERROR 3587:  Insufficient resources to execute plan on pool high_concurreny_pool [Request Too Large:Threads Exceeded: Requested = 5, Free = 0 (Limit = 0, Used = 0); File Handles Exceeded: Requested = 2, Free = 0 (Limit = 0, Used = 0); Memory(KB) Exceeded: Requested = 6916, Free = 1 (Limit = 1, Used = 0)]