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> );
Internal Bug reference MPP-18502