Understanding default values of general configuration parameters related to resource queue

posted Apr 28, 2017, 10:00 AM by Sachchida Ojha   [ updated Apr 28, 2017, 10:04 AM ]
sachi=# show max_resource_portals_per_transaction;
(1 row)
max_resource_portals_per_transaction Sets the maximum number of simultaneously open cursors allowed per transaction. Note that an open cursor will hold an active query slot in a resource queue.

sachi=# show max_resource_queues;
(1 row)

max_resource_queues parameter Sets the maximum number of resource queues.
sachi=# show resource_select_only;
(1 row)

a) If resource_select_only parameter is set to on means only SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR commands are evaluated. 
b) If resource_select_only parameter is set off means INSERT, UPDATE, and DELETE commands will be evaluated as well.

sachi=# show resource_cleanup_gangs_on_wait;
(1 row)

If resource_cleanup_gangs_on_wait is set to on means it cleans up idle segment worker processes before taking a slot in the resource queue.
sachi=# show stats_queue_level;
(1 row)

If set to on, stats_queue_level enables statistics collection on resource queue usage, which can then be viewed by querying the pg_stat_resqueues system view.