src/test/regress/expected/resource_queue.out

-- SQL coverage of RESOURCE QUEUE
-- start_ignore
create extension if not exists gp_inject_fault;
-- end_ignore
-- check we have correct initial state of the default resource queue
SELECT rqc.* FROM pg_resqueuecapability rqc JOIN pg_resqueue rq ON rqc.resqueueid = rq.oid WHERE rq.rsqname = 'pg_default';
 resqueueid | restypid | ressetting 
------------+----------+------------
       6055 |        5 | medium
       6055 |        6 | -1
(2 rows)

CREATE RESOURCE QUEUE regressq ACTIVE THRESHOLD 1;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |           -1 | f             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 2 COST THRESHOLD 2000.00;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             2 |         2000 | f             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq COST THRESHOLD 3000.00 OVERCOMMIT;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             2 |         3000 | t             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq COST THRESHOLD 4e+3 NOOVERCOMMIT;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             2 |         4000 | f             |                  0
(1 row)

COMMENT ON RESOURCE QUEUE regressq IS 'regressq comment';
DROP RESOURCE QUEUE regressq;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
---------+---------------+--------------+---------------+--------------------
(0 rows)

-- more coverage
CREATE RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000);
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |         2000 | f             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITHOUT (max_cost);
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |           -1 | f             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000)
WITHOUT (overcommit); -- negative
ERROR:  option "overcommit" is not a valid resource type
ALTER RESOURCE QUEUE regressq ACTIVE THRESHOLD 1 WITH (max_cost=2000)
WITHOUT (cost_overcommit); -- works
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |         2000 | f             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq OVERCOMMIT WITH (max_cost=2000);
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |         2000 | t             |                  0
(1 row)

ALTER RESOURCE QUEUE regressq IGNORE THRESHOLD 1 WITHOUT (max_cost);
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
----------+---------------+--------------+---------------+--------------------
 regressq |             1 |           -1 | t             |                  1
(1 row)

ALTER RESOURCE QUEUE regressq WITH (priority=high);
SELECT * FROM pg_resqueue_attributes WHERE rsqname='regressq';
 rsqname  |      resname      | ressetting | restypid 
----------+-------------------+------------+----------
 regressq | active_statements | 1          |        1
 regressq | max_cost          | -1         |        2
 regressq | min_cost          | 1          |        3
 regressq | cost_overcommit   | 1          |        4
 regressq | priority          | high       |        5
 regressq | memory_limit      | -1         |        6
(6 rows)

ALTER RESOURCE QUEUE regressq WITH (priority='MeDiUm');
SELECT * FROM pg_resqueue_attributes WHERE rsqname='regressq';
 rsqname  |      resname      | ressetting | restypid 
----------+-------------------+------------+----------
 regressq | active_statements | 1          |        1
 regressq | max_cost          | -1         |        2
 regressq | min_cost          | 1          |        3
 regressq | cost_overcommit   | 1          |        4
 regressq | priority          | MeDiUm     |        5
 regressq | memory_limit      | -1         |        6
(6 rows)

ALTER RESOURCE QUEUE regressq;
ERROR:  at least one threshold, overcommit or ignore limit must be specified
DROP RESOURCE QUEUE regressq;
SELECT rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit FROM pg_resqueue WHERE rsqname='regressq';
 rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
---------+---------------+--------------+---------------+--------------------
(0 rows)

-- negative
CREATE RESOURCE QUEUE regressq2;
ERROR:  at least one threshold ("ACTIVE_STATEMENTS", "MAX_COST") must be specified
CREATE RESOURCE QUEUE none ACTIVE THRESHOLD 2;
ERROR:  resource queue name "none" is reserved
;
CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2;
ALTER RESOURCE QUEUE regressq2 ACTIVE THRESHOLD -10;
ERROR:  active threshold cannot be less than -1 or equal to 0
ALTER RESOURCE QUEUE regressq2 COST THRESHOLD -1000.00;
ERROR:  cost threshold must be equal to -1 or greater than 0
ALTER RESOURCE QUEUE regressq2 WITH(max_cost=20,max_cost=21);
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE regressq2 WITH(PRIORITY=funky);
ERROR:  Invalid parameter value "funky" for resource type "PRIORITY"
ALTER RESOURCE QUEUE regressq2 WITHOUT(PRIORITY);
ERROR:  option "priority" cannot be disabled
DROP RESOURCE QUEUE regressq2;
CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD -10;
ERROR:  active threshold cannot be less than -1 or equal to 0
CREATE RESOURCE QUEUE regressq2 COST THRESHOLD -1000.00;
ERROR:  cost threshold cannot be less than -1 or equal to 0
CREATE RESOURCE QUEUE regressq2 IGNORE THRESHOLD -10;
ERROR:  min_cost threshold cannot be negative
CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2 ACTIVE THRESHOLD 2;
ERROR:  conflicting or redundant options
CREATE RESOURCE QUEUE regressq2 COST THRESHOLD 2 COST THRESHOLD 2;
ERROR:  conflicting or redundant options
CREATE RESOURCE QUEUE regressq2 OVERCOMMIT OVERCOMMIT;
ERROR:  conflicting or redundant options
CREATE RESOURCE QUEUE regressq2 OVERCOMMIT NOOVERCOMMIT;
ERROR:  conflicting or redundant options
CREATE RESOURCE QUEUE regressq2 IGNORE THRESHOLD 1 IGNORE THRESHOLD 1 ;
ERROR:  conflicting or redundant options
CREATE RESOURCE QUEUE regressq2 WITH (WITHLISTSTART=funky);
ERROR:  option "withliststart" is not a valid resource type
CREATE RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2;
ALTER RESOURCE QUEUE regressq2 ACTIVE THRESHOLD 2 ACTIVE THRESHOLD 2;
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE regressq2 COST THRESHOLD 2 COST THRESHOLD 2;
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE regressq2 OVERCOMMIT OVERCOMMIT;
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE regressq2 OVERCOMMIT NOOVERCOMMIT;
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE regressq2 IGNORE THRESHOLD 1 IGNORE THRESHOLD 1 ;
ERROR:  conflicting or redundant options
ALTER RESOURCE QUEUE none IGNORE THRESHOLD 1 ;
ERROR:  resource queue "none" does not exist
DROP RESOURCE QUEUE regressq2;
-- Create resource queue with cost_overcommit=true
create resource queue t3_test_q with (active_statements = 6,max_cost=5e+06 ,cost_overcommit=true, min_cost=50000);
select rsqname, rsqcountlimit, rsqcostlimit, rsqovercommit, rsqignorecostlimit from pg_resqueue where rsqname='t3_test_q';
  rsqname  | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
-----------+---------------+--------------+---------------+--------------------
 t3_test_q |             6 |        5e+06 | t             |              50000
(1 row)

drop resource queue t3_test_q;
-- Resource Queue should not be created inside Transaction block the error is the expected behavior
begin;
CREATE RESOURCE QUEUE db_resque_new1 ACTIVE THRESHOLD 2 COST THRESHOLD 2000.00;
ERROR:  CREATE RESOURCE QUEUE cannot run inside a transaction block
end;
--
-- memory quota feature
--
-- negative
create resource queue test_rq with (max_cost=2000000, memory_limit='1gB'); -- should error out
ERROR:  Invalid parameter value "1gB" for resource type "MEMORY_LIMIT". Value must be in kB, MB or GB.
create resource queue test_rq with (max_cost=2000000, memory_limit='0'); -- should error out
ERROR:  Invalid parameter value "0" for resource type "MEMORY_LIMIT". Value must be at least 10240kB
-- 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');
--
-- CPU priority feature
--
create resource queue test_rq_cpu with (active_statements=2, priority='HIGH');
create user test_rp_user with login;
alter role test_rp_user resource queue test_rq_cpu;
SET ROLE test_rp_user;
-- query priority and weight on master
select rqppriority, rqpweight from gp_toolkit.gp_resq_priority_backend where rqpsession in (select sess_id from pg_stat_activity where pid  = pg_backend_pid());
 rqppriority | rqpweight 
-------------+-----------
 HIGH        |      1000
(1 row)

-- query priority and weight on segments
select rqppriority, rqpweight from gp_dist_random('gp_toolkit.gp_resq_priority_backend') where rqpsession in (select sess_id from pg_stat_activity where pid  = pg_backend_pid());
 rqppriority | rqpweight 
-------------+-----------
 HIGH        |      1000
 HIGH        |      1000
 HIGH        |      1000
(3 rows)

RESET ROLE;
drop user test_rp_user;
drop resource queue test_rq_cpu;
-- Alters
alter resource queue test_rq with (memory_limit='1024mb');
ERROR:  Invalid parameter value "1024mb" for resource type "MEMORY_LIMIT". Value must be in kB, MB or GB.
alter resource queue test_rq with (memory_limit='1024Kb');
ERROR:  Invalid parameter value "1024Kb" for resource type "MEMORY_LIMIT". Value must be in kB, MB or GB.
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';
 rolname | rolsuper |  rsqname  
---------+----------+-----------
 reg_u1  | f        | reg_costq
(1 row)

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';
 rolname | rolsuper |   rsqname   
---------+----------+-------------
 reg_u1  | f        | reg_activeq
(1 row)

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';
 rolname | rolsuper |   rsqname   
---------+----------+-------------
 reg_u2  | f        | reg_activeq
 reg_u1  | f        | reg_activeq
(2 rows)

-- negative
CREATE USER reg_u3 RESOURCE QUEUE bogusq;
ERROR:  resource queue "bogusq" does not exist
-- feature must be on for tests to be valid
show resource_scheduler;
 resource_scheduler 
--------------------
 on
(1 row)

-- 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
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 576196, portal id: 3
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;
 ?column? 
----------
        4
(1 row)

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;
  rsqname  | rsqholders 
-----------+------------
 reg_costq |          2
(1 row)

DECLARE c2 CURSOR FOR SELECT * FROM tenk1 a NATURAL JOIN tenk1 b;
SELECT rsqname, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0;
  rsqname  | rsqholders 
-----------+------------
 reg_costq |          3
(1 row)

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;
  rsqname  | rsqholders 
-----------+------------
 reg_costq |          1
(1 row)

-- 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;
  rsqname  | rsqcostlimit | rsqwaiters | rsqholders 
-----------+--------------+------------+------------
 reg_costq |        30000 |          0 |          2
(1 row)

DECLARE c2 CURSOR FOR SELECT * FROM tenk1;
SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0;
  rsqname  | rsqcostlimit | rsqwaiters | rsqholders 
-----------+--------------+------------+------------
 reg_costq |        30000 |          0 |          3
(1 row)

DECLARE c3 CURSOR FOR SELECT * FROM tenk1 a, tenk1 b;
ERROR:  statement requires more resources than resource queue allows
DETAIL:  resource queue id: 625696, portal id: 3
SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
DECLARE c4 CURSOR FOR SELECT * FROM tenk1 a, tenk1 b, tenk1 c;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
SELECT rsqname, rsqcostlimit, rsqwaiters, rsqholders FROM pg_resqueue_status WHERE rsqcostvalue > 0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
END;
SELECT rsqname, rsqholders FROM pg_resqueue_status where rsqcostvalue != 0 or rsqcountvalue != 0 or rsqholders != 0; -- 1 row expected
  rsqname  | rsqholders 
-----------+------------
 reg_costq |          1
(1 row)

-- 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;
-- catalog tests
set optimizer_enable_master_only_queries = on;
select count(*)/1000 from
(select
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='priority') as "Priority",
(select count(*) from pg_resqueue x,pg_roles y
where x.oid=y.rolresqueue and a.rsqname=x.rsqname) as "RQAssignedUsers"
from ( select distinct rsqname from pg_resqueue_attributes ) a)
as foo;
 ?column? 
----------
        0
(1 row)

select count(*)/1000 from
(select a.rsqname as "RQname",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='active_statements') as "ActiveStatment",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='max_cost') as "MaxCost",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='min_cost') as "MinCost",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='cost_overcommit') as "CostOvercommit",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='memory_limit') as "MemoryLimit",
(select ressetting from pg_resqueue_attributes b
where a.rsqname=b.rsqname and resname='priority') as "Priority",
(select count(*) from pg_resqueue x,pg_roles y
where x.oid=y.rolresqueue and a.rsqname=x.rsqname) as "RQAssignedUsers"
from ( select distinct rsqname from pg_resqueue_attributes ) a)
as foo;
 ?column? 
----------
        0
(1 row)

reset optimizer_enable_master_only_queries;
-- 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;
 pcolor | pname | pn  
--------+-------+-----
 Black  | Dream | 200
(1 row)

fetch c0;
 pcolor | pname | pn  
--------+-------+-----
 Black  | Sword | 100
(1 row)

fetch c0;
  pcolor   | pname  | pn  
-----------+--------+-----
 Chocolate | Donuts | 600
(1 row)

select * from rq_product;
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 576200, portal id: 0
fetch c0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
abort;
begin;
declare c0 cursor for select pcolor, pname, pn from rq_product order by 1,2,3;
fetch c0;
 pcolor | pname | pn  
--------+-------+-----
 Black  | Dream | 200
(1 row)

fetch c0;
 pcolor | pname | pn  
--------+-------+-----
 Black  | Sword | 100
(1 row)

fetch c0;
  pcolor   | pname  | pn  
-----------+--------+-----
 Chocolate | Donuts | 600
(1 row)

fetch c0;
 pcolor |  pname  | pn  
--------+---------+-----
 Clear  | Justice | 400
(1 row)

select * from rq_product;
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 576200, portal id: 0
fetch c0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
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;
 pcolor | pname | pn  | n | l0 | l1 | l2 | l3 | l4 | l5 | l6 | l7 | l8 | l9 | l10 | l11 | l12 | l13 | l14 | l15 | l16 | l17 | l18 | l19 | l20 | l21 | l22 | l23 | l24 | l25 | l26 | l27 | l28 | l29 | l30 | l31 | l32 
--------+-------+-----+---+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 Black  | Dream | 200 | 1 |    |    |    |    |    |    |    |    |    |    |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |    
(1 row)

select * from rq_product;
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 576200, portal id: 0
fetch c0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
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;
 pcolor | pname | pn  | n | l0 | l1 | l2 | l3 | l4 | l5 | l6 | l7 | l8 | l9 | l10 | l11 | l12 | l13 | l14 | l15 | l16 | l17 | l18 | l19 | l20 | l21 | l22 | l23 | l24 | l25 | l26 | l27 | l28 | l29 | l30 | l31 | l32 
--------+-------+-----+---+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 Black  | Dream | 200 | 1 |    |    |    |    |    |    |    |    |    |    |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |     |    
(1 row)

select * from rq_product;
ERROR:  deadlock detected, locking against self
DETAIL:  resource queue id: 576200, portal id: 0
fetch c0;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
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;
-- Coverage for resource queue error conditions
CREATE ROLE rq_test_oosm_role;
NOTICE:  resource queue required -- using default resource queue "pg_default"
SET ROLE rq_test_oosm_role;
CREATE TABLE rq_test_oosm_table(i int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' 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 rq_test_oosm_table VALUES(1);
-- Simulate an out-of-shared-memory condition during the course of grabbing a
-- resource queue lock (in ResLockAcquire()).
SELECT gp_inject_fault('res_increment_add_oosm', 'skip', 1);
 gp_inject_fault 
-----------------
 Success:
(1 row)

-- Queries should error out indicating that we have no shared memory left.
SELECT * FROM rq_test_oosm_table;
ERROR:  out of shared memory adding portal increments
HINT:  You may need to increase max_resource_portals_per_transaction.
SELECT gp_inject_fault('res_increment_add_oosm', 'reset', 1);
 gp_inject_fault 
-----------------
 Success:
(1 row)

-- Queries should succeed now.
SELECT * FROM rq_test_oosm_table;
 i 
---
 1
(1 row)

DROP TABLE rq_test_oosm_table;
RESET ROLE;
DROP ROLE rq_test_oosm_role;
-- test for extended queries
-- create a role that only use in this test will drop it later
-- later we will use username to identify the backend process
create role extend_protocol_requeue_role with login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
-- start_matchsubs
--
-- m/NOTICE:  query requested \d+/
-- s/NOTICE:  query requested \d+/NOTICE:  query requested XXX/g
--
-- m/NOTICE:  SPI memory reservation \d+/
-- s/NOTICE:  SPI memory reservation \d+/NOTICE:  SPI memory reservation /g
--
-- end_matchsubs
-- run query using non_superuser role so that it can be
-- controled by resource queue
\! ./extended_protocol_resqueue dbname=regression extend_protocol_requeue_role;
NOTICE:  query requested 9128000KB
NOTICE:  query requested 9128000KB of memory
NOTICE:  SPI memory reservation 1931072000
drop role extend_protocol_requeue_role;