Pivotal Greenplum Database (GPDB) all versions
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
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.
- 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.