In Which Scenarios Would a GaussDB(DWS) Statement Be "idle in transaction"?

When user SQL information is queried in the PGXC_STAT_ACTIVITY view, the state column in the query result sometimes shows idle in transaction. idle in transaction indicates that the backend is in a transaction, but no statement is being executed. This status indicates that a statement has been executed. Therefore, the value of query_id is 0, but the transaction has not been committed or rolled back. Statements in this state have been executed and do not occupy CPU and I/O resources, but they occupy connection resources such as connections and concurrent connections.

If a statement is in the idle in transaction state, rectify the fault by referring to the following common scenarios and solutions:

1

SELECT state, query, query_id FROM pgxc_stat_activity;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE OR REPLACE FUNCTION public.test_sleep()
RETURNS void
LANGUAGE plpgsql
AS $$

BEGIN
    truncate t1;
    truncate t2;
    EXECUTE IMMEDIATE 'select pg_sleep(6)';
	RETURN;
END$$;

1

SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack';

1

SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack';

Solution:

SAVEPOINT and RELEASE statements are automatically generated by the system when a stored procedure with EXCEPTION is executed. In versions later than 8.1.0, SAVEPOINT is not delivered to CNs. GaussDB(DWS) stored procedures with EXCEPTION are implemented based on subtransactions, the mapping is as follows:

1
2
3
4
5
6
7
8

begin
    (Savepoint s1)
    DDL/DML
exception
    (Rollback to s1)
    (Release s1)
...
end

If there is EXCEPTION in a stored procedure when it is started, a subtransaction will be started. If there is and exception during the execution, the current transaction is rolled back and the exception is handled; if there is no exception, the subtransaction is committed.

This problem may occur when there are many such stored procedures and the stored procedures are nested. Similar to scenario 2, you only have to wait after the entire stored procedure is executed. If there are a large number of RELEASE messages, the stored procedure triggered multiple exceptions. In this case, you must re-examine the logic of the stored procedure.