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

Answers

• Jim_Knicely - Select Field - Administrator

  May 2018

  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.

  0
• Jim_Knicely - Select Field - Administrator

  May 2018 edited May 2018

  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)]
  

  0