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 using cursors.
The resource queue logic has been be redesigned and will be available in 5.x.
1. Customers needs to tune the resource queues and increase active statements as a workaround.
2. Customers can try to increase the GUC deadlock_timeout which helps to avoid marking some non genuine scenarios involving resource queue slot lock as deadlock.For more detail on this you can refer GPDB deadlock detection with Resource queue.