src/test/regress/expected/resource_queue_function.source

CREATE FUNCTION checkResourceQueueMemoryLimits(cstring) RETURNS boolean
AS '@abs_srcdir@/regress.so', 'checkResourceQueueMemoryLimits' LANGUAGE C READS SQL DATA;
CREATE TABLE test_table(c1 int, c2 int); 
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO test_table values(1, 2);
-- MPP-17240
CREATE RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS = 1, MEMORY_LIMIT='10MB');
CREATE ROLE test_role WITH RESOURCE QUEUE test_q;
GRANT SELECT ON test_table TO test_role;
SET STATEMENT_MEM = '125MB';
-- should return true
select checkResourceQueueMemoryLimits('test_q'); 
 checkresourcequeuememorylimits 
--------------------------------
 t
(1 row)

SET ROLE test_role; 
-- should deadlock as there aren't enough resources to run the query.
-- query should be assigned 125MB, but queue only allows 10MB 
SELECT count(*) FROM test_table; 
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 581950, portal id: 0
RESET ROLE;
-- should return true
select checkResourceQueueMemoryLimits('test_q'); 
 checkresourcequeuememorylimits 
--------------------------------
 t
(1 row)

RESET STATEMENT_MEM;
REVOKE SELECT ON test_table FROM test_role;
DROP ROLE test_role; 
DROP RESOURCE QUEUE test_q;
-- MPP-15992
CREATE RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS = 1, MEMORY_LIMIT='8GB');
CREATE ROLE test_role WITH RESOURCE QUEUE test_q;
GRANT SELECT ON test_table TO test_role;
SET ROLE test_role; 
-- should return result 
SELECT count(*) FROM test_table; 
 count 
-------
     1
(1 row)

RESET ROLE;
ALTER RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS = 1, MEMORY_LIMIT='10GB');
SET ROLE test_role; 
-- should return result 
SELECT count(*) FROM test_table; 
 count 
-------
     1
(1 row)

RESET ROLE;
-- should return true
select checkResourceQueueMemoryLimits('test_q'); 
 checkresourcequeuememorylimits 
--------------------------------
 t
(1 row)

ALTER RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS = 1, MEMORY_LIMIT='7GB');
SET ROLE test_role; 
-- should return result 
SELECT count(*) FROM test_table; 
 count 
-------
     1
(1 row)

RESET ROLE;
-- should return true
select checkResourceQueueMemoryLimits('test_q'); 
 checkresourcequeuememorylimits 
--------------------------------
 t
(1 row)

REVOKE SELECT ON test_table FROM test_role;
DROP TABLE test_table;
DROP ROLE test_role; 
DROP RESOURCE QUEUE test_q;
DROP FUNCTION checkResourceQueueMemoryLimits(cstring);