Query "<IDLE> in transaction" Uses a Resource Queue Slot

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

<IDLE> in transaction session is using a resource queue slot:

flightdata=# select datname, transaction, rsqname, rsqcountlimit, rsqcountvalue, rsqwaiters, rsqholders, procpid, sess_id, usename, current_query from pg_locks, gp_toolkit.gp_resqueue_status, pg_stat_activity WHERE pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid AND pg_stat_activity.procpid=pg_locks.pid AND current_query like '%in transaction%';
 datname | transaction | rsqname | rsqcountlimit | rsqcountvalue | rsqwaiters | rsqholders | procpid | sess_id | usename | current_query
---------+-------------+---------+---------------+---------------+------------+------------+---------+---------+---------+-----------------------
 rq | 1888 | myqueue | 2 | 1 | 0 | 1 | 32703 | 208 | u1 | <IDLE> in transaction
(1 row) 

<IDLE> in transaction session is created when you start the transaction in the DB by using BEGIN statement. This session should not use a resource queue slot by default as they don't execute any query even if they are in the transaction cycle.

 

Environment

Cause

The user declares a cursor when in the transaction.

If the cursor is declared then the result will stay in memory and the query will use a slot in a resource queue even if it is idle.

Example:

rq=> begin;
BEGIN
rq=> SELECT * from pg_cursors ;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)
rq=> DECLARE c CURSOR with hold for select 1;

<IDLE> in the transaction is now visible in the resource queue:

flightdata=# select datname, transaction, rsqname, rsqcountlimit, rsqcountvalue, rsqwaiters, rsqholders, procpid, sess_id, usename, current_query from pg_locks, gp_toolkit.gp_resqueue_status, pg_stat_activity WHERE pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid AND pg_stat_activity.procpid=pg_locks.pid AND current_query like '%in transaction%';
 datname | transaction | rsqname | rsqcountlimit | rsqcountvalue | rsqwaiters | rsqholders | procpid | sess_id | usename | current_query
---------+-------------+---------+---------------+---------------+------------+------------+---------+---------+---------+-----------------------
 rq | 1888 | myqueue | 2 | 1 | 0 | 1 | 32703 | 208 | u1 | <IDLE> in transaction
(1 row)

 If used without cursor

rq=> begin;
BEGIN
flightdata=# select datname, transaction, rsqname, rsqcountlimit, rsqcountvalue, rsqwaiters, rsqholders, procpid, sess_id, usename, current_query from pg_locks, gp_toolkit.gp_resqueue_status, pg_stat_activity WHERE pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid AND pg_stat_activity.procpid=pg_locks.pid AND current_query like '%in transaction%';
 datname | transaction | rsqname | rsqcountlimit | rsqcountvalue | rsqwaiters | rsqholders | procpid | sess_id | usename | current_query
---------+-------------+---------+---------------+---------------+------------+------------+---------+---------+---------+---------------
(0 rows)

 

Resolution

Use cursor with caution knowing they will use a resource queue slot. The slot is released when the transaction is committed.