Executing a function under RQ controlled results in "ERROR: deadlock detected, locking against self"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

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

ERROR:  deadlock detected, locking against self

A reproduction of the issue is shown below:
 

The function or Script is displayed below:

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 with the following commands:

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

Connect to the database as the user. 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

Environment

Cause

This is the expected behavior of the resource queue and functions. The various statements of the Resource_Queue function are executed separately. The error is produced when a user is restricted to more than one active connection (RQ active_statements limit).

Resolution

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

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

Note: This issue is 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.