Environment

Symptom

Attempting to execute a function under a user that is restricted by the active_statements of the resource queue results in the following error message:

ERROR:  deadlock detected, locking against self

Refer to a reproduction of the issue is below:

-- The function or Script:

begin;
declare c1 cursor for select 1;
fetch 10 in c1;
declare c2 cursor for select 1;
fetch 10 in c2;
commit;

-- Create the user and its resource queue:

create resource queue one with (active_statements=1);
create user cognos with RESOURCE QUEUE one;

-- Connect as the user and execution of the query results in the error:

[gpadmin@mdw2 ~]$ psql -U cognos -f sql
Timing is on.
Pager usage is off.
BEGIN
Time: 259.295 ms
DECLARE CURSOR
Time: 590.509 ms
 yr_num | attrib_num | case | qtr_dt_key 
--------+------------+------+------------
(0 rows)

Time: 513.258 ms
psql:sql:22: ERROR:  deadlock detected, locking against self
psql:sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
Time: 0.082 ms

Cause

This is the expected behavior of the Resource Queue and functions; the various statements of the Resource_Queue function are executed separately and when the user is restricted to more than one active connection (RQ active_statements limit), it will result in the error above. 

Resolution

Increase the value of the active_statements in the resource queue using the command below.

ALTER RESOURCE QUEUE xxx WITH ( active_statements=<value> ); 

Additional information

Internal Bug reference MPP-18502