| -- SQL coverage of RESOURCE QUEUE |
| |
| CREATE RESOURCE QUEUE regressq ACTIVE THRESHOLD 1; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 2 COST THRESHOLD 2000.00; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq COST THRESHOLD 3000.00 OVERCOMMIT; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq COST THRESHOLD 4e+3 NOOVERCOMMIT; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| DROP RESOURCE QUEUE regressq; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| |
| -- more coverage |
| CREATE RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000); |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITHOUT (max_cost); |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000) |
| WITHOUT (overcommit); -- negative |
| ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000) |
| WITHOUT (cost_overcommit); -- works |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq OVERCOMMIT WITH (max_cost=2000); |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq IGNORE THRESHOLD 1 WITHOUT (max_cost); |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq WITH (priority=high); |
| SELECT * FROM pg_resqueue_attributes WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq WITH (priority='MeDiUm'); |
| SELECT * FROM pg_resqueue_attributes WHERE rsqname='regressq'; |
| ALTER RESOURCE QUEUE regressq; |
| DROP RESOURCE QUEUE regressq; |
| SELECT * FROM pg_resqueue WHERE rsqname='regressq'; |
| |
| -- negative |
| |
| CREATE RESOURCE QUEUE regressq2; |
| CREATE RESOURCE QUEUE none ACTIVE THRESHOLD 2; |
| ; |
| CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2; |
| ALTER RESOURCE QUEUE regressq2 ACTIVE THRESHOLD -10; |
| ALTER RESOURCE QUEUE regressq2 COST THRESHOLD -1000.00; |
| ALTER RESOURCE QUEUE regressq2 WITH(max_cost=20,max_cost=21); |
| ALTER RESOURCE QUEUE regressq2 WITH(PRIORITY=funky); |
| ALTER RESOURCE QUEUE regressq2 WITHOUT(PRIORITY); |
| DROP RESOURCE QUEUE regressq2; |
| |
| CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD -10; |
| CREATE RESOURCE QUEUE regressq2 COST THRESHOLD -1000.00; |
| CREATE RESOURCE QUEUE regressq2 IGNORE THRESHOLD -10; |
| |
| CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2 ACTIVE THRESHOLD 2; |
| CREATE RESOURCE QUEUE regressq2 COST THRESHOLD 2 COST THRESHOLD 2; |
| CREATE RESOURCE QUEUE regressq2 OVERCOMMIT OVERCOMMIT; |
| CREATE RESOURCE QUEUE regressq2 OVERCOMMIT NOOVERCOMMIT; |
| CREATE RESOURCE QUEUE regressq2 IGNORE THRESHOLD 1 IGNORE THRESHOLD 1 ; |
| |
| CREATE RESOURCE QUEUE regressq2 WITH (WITHLISTSTART=funky); |
| |
| CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2; |
| ALTER RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2 ACTIVE THRESHOLD 2; |
| ALTER RESOURCE QUEUE regressq2 COST THRESHOLD 2 COST THRESHOLD 2; |
| ALTER RESOURCE QUEUE regressq2 OVERCOMMIT OVERCOMMIT; |
| ALTER RESOURCE QUEUE regressq2 OVERCOMMIT NOOVERCOMMIT; |
| ALTER RESOURCE QUEUE regressq2 IGNORE THRESHOLD 1 IGNORE THRESHOLD 1 ; |
| |
| ALTER RESOURCE QUEUE none IGNORE THRESHOLD 1 ; |
| DROP RESOURCE QUEUE regressq2; |
| |
| -- |
| -- memory quota feature |
| -- |
| |
| -- negative |
| |
| create resource queue test_rq with (max_cost=2000000, memory_limit='1gB'); -- should error out |
| create resource queue test_rq with (max_cost=2000000, memory_limit='0'); -- should error out |
| |
| -- Creates and drops |
| |
| create resource queue test_rq with (active_statements=2); |
| drop resource queue test_rq; |
| create resource queue test_rq with (active_statements=2, memory_limit='1024MB'); |
| |
| -- Alters |
| |
| alter resource queue test_rq with (memory_limit='1024mb'); |
| alter resource queue test_rq with (memory_limit='1024Kb'); |
| alter resource queue test_rq with (memory_limit='2GB'); |
| alter resource queue test_rq without (memory_limit); |
| |
| drop resource queue test_rq; |
| |
| -- SQL coverage of ROLE -> RESOURCE QUEUE |
| |
| CREATE RESOURCE QUEUE reg_activeq ACTIVE THRESHOLD 2; |
| CREATE RESOURCE QUEUE reg_costq COST THRESHOLD 30000.00; |
| CREATE USER reg_u1 RESOURCE QUEUE reg_costq; |
| GRANT SELECT ON tenk1 TO reg_u1; |
| SELECT u.rolname, u.rolsuper, r.rsqname FROM pg_roles as u, pg_resqueue as r WHERE u.rolresqueue=r.oid and rolname='reg_u1'; |
| ALTER USER reg_u1 RESOURCE QUEUE reg_activeq; |
| SELECT u.rolname, u.rolsuper, r.rsqname FROM pg_roles as u, pg_resqueue as r WHERE u.rolresqueue=r.oid and rolname='reg_u1'; |
| CREATE USER reg_u2 RESOURCE QUEUE reg_activeq; |
| GRANT SELECT ON tenk1 TO reg_u2; |
| SELECT u.rolname, u.rolsuper, r.rsqname FROM pg_roles as u, pg_resqueue as r WHERE u.rolresqueue=r.oid and r.rsqname='reg_activeq'; |
| |
| -- negative |
| |
| CREATE USER reg_u3 RESOURCE QUEUE bogusq; |
| |
| -- feature must be on for tests to be valid |
| show resource_scheduler; |
| |
| -- switch to a non privileged user for next tests |
| SET SESSION AUTHORIZATION reg_u1; |
| |
| -- self deadlock (active queue threshold 2) |
| BEGIN; |
| DECLARE c1 CURSOR FOR SELECT 1 FROM tenk1; |
| DECLARE c2 CURSOR FOR SELECT 2 FROM tenk1; |
| DECLARE c3 CURSOR FOR SELECT 3 FROM tenk1; -- should detect deadlock |
| END; |
| |
| -- track cursor open/close count (should not deadlock) |
| BEGIN; |
| DECLARE c1 CURSOR FOR SELECT 1 FROM tenk1; |
| CLOSE c1; |
| DECLARE c2 CURSOR FOR SELECT 2 FROM tenk1; |
| DECLARE c3 CURSOR FOR SELECT 3 FROM tenk1; |
| CLOSE c3; |
| DECLARE c4 CURSOR FOR SELECT 4 FROM tenk1; |
| FETCH c4; |
| END; |
| |
| -- switch to a cost-limited queue |
| RESET SESSION AUTHORIZATION; |
| ALTER USER reg_u2 RESOURCE QUEUE reg_costq; |
| SET SESSION AUTHORIZATION reg_u2; |
| BEGIN; |
| DECLARE c1 CURSOR FOR SELECT * FROM tenk1; |
| SELECT rsqname, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| DECLARE c2 CURSOR FOR SELECT * FROM tenk1 a NATURAL JOIN tenk1 b; |
| SELECT rsqname, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| CLOSE c1; |
| CLOSE c2; |
| END; |
| |
| -- rsqcostvalue should go back to 0 when queue is empty (MPP-3578) |
| SELECT rsqname, rsqholders FROM pg_resqueue_status where rsqcostvalue != 0 or rsqcountvalue != 0 or rsqholders != 0; |
| |
| -- MPP-3796. When a cursor exceeds the cost limit and the transaction is |
| -- aborted, resources which had already been granted to other cursors should |
| -- be released. Here there are no other concurrent transactions sharing the |
| -- queue, so rsqcostvalue should go back to 0. |
| BEGIN; |
| DECLARE c1 CURSOR FOR SELECT * FROM tenk1; |
| SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| DECLARE c2 CURSOR FOR SELECT * FROM tenk1; |
| SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| DECLARE c3 CURSOR FOR SELECT * FROM tenk1 a, tenk1 b; |
| SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| DECLARE c4 CURSOR FOR SELECT * FROM tenk1 a, tenk1 b, tenk1 c; |
| SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0; |
| END; |
| SELECT rsqname, rsqholders FROM pg_resqueue_status where rsqcostvalue != 0 or rsqcountvalue != 0 or rsqholders != 0; -- 1 row expected |
| |
| -- return to the super user |
| RESET SESSION AUTHORIZATION; |
| |
| -- reset session to super user. make sure no longer queued |
| BEGIN; |
| DECLARE c1 CURSOR FOR SELECT 1 FROM tenk1; |
| DECLARE c2 CURSOR FOR SELECT 2 FROM tenk1; |
| DECLARE c3 CURSOR FOR SELECT 3 FROM tenk1; -- should not deadlock, we are SU. |
| END; |
| |
| -- cleanup |
| |
| DROP OWNED BY reg_u1, reg_u2 CASCADE; |
| DROP USER reg_u1; |
| DROP USER reg_u2; |
| DROP RESOURCE QUEUE reg_activeq; |
| DROP RESOURCE QUEUE reg_costq; |
| |
| |
| -- Followup additional tests. |
| -- MPP-7474 |
| CREATE RESOURCE QUEUE rq_test_q ACTIVE THRESHOLD 1; |
| CREATE USER rq_test_u RESOURCE QUEUE rq_test_q; |
| |
| create table rq_product ( |
| pn int not null, |
| pname text not null, |
| pcolor text, |
| primary key (pn) |
| ) distributed by (pn); |
| |
| -- Products |
| insert into rq_product values |
| ( 100, 'Sword', 'Black'), |
| ( 200, 'Dream', 'Black'), |
| ( 300, 'Castle', 'Grey'), |
| ( 400, 'Justice', 'Clear'), |
| ( 500, 'Donuts', 'Plain'), |
| ( 600, 'Donuts', 'Chocolate'), |
| ( 700, 'Hamburger', 'Grey'), |
| ( 800, 'Fries', 'Grey'); |
| |
| GRANT SELECT ON rq_product TO rq_test_u; |
| |
| set session authorization rq_test_u; |
| |
| begin; |
| declare c0 cursor for select pcolor, pname, pn from rq_product order by 1,2,3; |
| |
| fetch c0; |
| fetch c0; |
| fetch c0; |
| select * from rq_product; |
| |
| fetch c0; |
| abort; |
| |
| begin; |
| declare c0 cursor for select pcolor, pname, pn from rq_product order by 1,2,3; |
| |
| fetch c0; |
| fetch c0; |
| fetch c0; |
| fetch c0; |
| select * from rq_product; |
| |
| fetch c0; |
| abort; |
| |
| begin; |
| declare c0 cursor for |
| select pcolor, pname, pn, |
| row_number() over (w) as n, |
| lag(pn+0) over (w) as l0, |
| lag(pn+1) over (w) as l1, |
| lag(pn+2) over (w) as l2, |
| lag(pn+3) over (w) as l3, |
| lag(pn+4) over (w) as l4, |
| lag(pn+5) over (w) as l5, |
| lag(pn+6) over (w) as l6, |
| lag(pn+7) over (w) as l7, |
| lag(pn+8) over (w) as l8, |
| lag(pn+9) over (w) as l9, |
| lag(pn+10) over (w) as l10, |
| lag(pn+11) over (w) as l11, |
| lag(pn+12) over (w) as l12, |
| lag(pn+13) over (w) as l13, |
| lag(pn+14) over (w) as l14, |
| lag(pn+15) over (w) as l15, |
| lag(pn+16) over (w) as l16, |
| lag(pn+17) over (w) as l17, |
| lag(pn+18) over (w) as l18, |
| lag(pn+19) over (w) as l19, |
| lag(pn+20) over (w) as l20, |
| lag(pn+21) over (w) as l21, |
| lag(pn+22) over (w) as l22, |
| lag(pn+23) over (w) as l23, |
| lag(pn+24) over (w) as l24, |
| lag(pn+25) over (w) as l25, |
| lag(pn+26) over (w) as l26, |
| lag(pn+27) over (w) as l27, |
| lag(pn+28) over (w) as l28, |
| lag(pn+29) over (w) as l29, |
| lag(pn+30) over (w) as l30, |
| lag(pn+31) over (w) as l31, |
| lag(pn+32) over (w) as l32 |
| from rq_product |
| window w as (partition by pcolor order by pname) order by 1,2,3; |
| |
| fetch c0; |
| select * from rq_product; |
| |
| fetch c0; |
| abort; |
| |
| begin; |
| create view window_view as |
| select pcolor, pname, pn, |
| row_number() over (w) as n, |
| lag(pn+0) over (w) as l0, |
| lag(pn+1) over (w) as l1, |
| lag(pn+2) over (w) as l2, |
| lag(pn+3) over (w) as l3, |
| lag(pn+4) over (w) as l4, |
| lag(pn+5) over (w) as l5, |
| lag(pn+6) over (w) as l6, |
| lag(pn+7) over (w) as l7, |
| lag(pn+8) over (w) as l8, |
| lag(pn+9) over (w) as l9, |
| lag(pn+10) over (w) as l10, |
| lag(pn+11) over (w) as l11, |
| lag(pn+12) over (w) as l12, |
| lag(pn+13) over (w) as l13, |
| lag(pn+14) over (w) as l14, |
| lag(pn+15) over (w) as l15, |
| lag(pn+16) over (w) as l16, |
| lag(pn+17) over (w) as l17, |
| lag(pn+18) over (w) as l18, |
| lag(pn+19) over (w) as l19, |
| lag(pn+20) over (w) as l20, |
| lag(pn+21) over (w) as l21, |
| lag(pn+22) over (w) as l22, |
| lag(pn+23) over (w) as l23, |
| lag(pn+24) over (w) as l24, |
| lag(pn+25) over (w) as l25, |
| lag(pn+26) over (w) as l26, |
| lag(pn+27) over (w) as l27, |
| lag(pn+28) over (w) as l28, |
| lag(pn+29) over (w) as l29, |
| lag(pn+30) over (w) as l30, |
| lag(pn+31) over (w) as l31, |
| lag(pn+32) over (w) as l32 |
| from rq_product |
| window w as (partition by pcolor order by pname); |
| |
| DECLARE c0 cursor for select * from window_view order by 1,2,3; |
| fetch c0; |
| select * from rq_product; |
| |
| fetch c0; |
| abort; |
| |
| RESET SESSION_AUTHORIZATION; |
| |
| DROP OWNED BY rq_test_u CASCADE; |
| DROP USER rq_test_u; |
| DROP RESOURCE QUEUE rq_test_q; |
| DROP TABLE rq_product; |
| |
