Pivotal Knowledge Base

Follow

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

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Symptom

<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 are 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.

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 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.

Comments

Powered by Zendesk