Environment

Pivotal Greenplum Database (GPDB) all versions

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

• This issue will be addressed in GPDB 5.x Resource Group. In GPDB 4.3.x, statement level concurrency control is used for Resource Queue. In GPDB 5.x, transaction level concurrency control is used for Resource Group.

Additional information

Related issue and workaround