|Pivotal Greenplum (GPDB)||4.3.x|
User assign to a resource queue get a deadlock message.
"ERROR","40P01","deadlock detected","Process 76022 waits for ExclusiveLock on resource queue 9140473; blocked by process 75991. Process 75973 waits for ExclusiveLock on resource queue 9140473; blocked by process 76022."
Most of the time this is a result of a user defined function with cursor calling another user defined function while logged in as a user assigned to resource queue.
it is a known limitation of current resource queue implementation that it is possible to be in deadlock if you use functions that execute another SQL
At the time of writing this article (29.09.16) There is no available resolution. The resource queue logic will be redesigned in the future and engineering is aware of the issue.
Workaround: Customers needs to tune the resource queues and increase active statements as a workaround .