May 2018
Intermediate to advanced
576 pages
30h 25m
English
A session which is not running any query is in the idle state if outside of a transaction, or in the idle in transaction state if it is inside a transaction, that is, between a BEGIN and the corresponding COMMIT. Recall recipe Writing a script that either succeeds entirely or fails entirely in Chapter 7, Database Administration, for how BEGIN and COMMIT can be used to wrap several commands into one transaction.
The reason to distinguish between these two states is that locks are released at the end of a transaction. Hence an idle in transaction session is not currently doing anything, but it might be preventing other queries, including VACUUM, from accessing some tables.