Re: proc state as "Idle_in_transaction" in pg_stat_activity

On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:

Hi All,

Good Evening.

Could someone please suggest the process to resolve the issue.

Issue : proc state as "Idle_in_transaction"

Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).

We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.

If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.

proc: select msg,rescode from schema.proc_name('arg1','arg2');

If you run just this in psql, you will be in autocommit mode.  The statement will run in its own transaction which commit as soon as the statement finishes.

The problem is not with the line you show,  it is in what happens before (a transaction is opened) and after (it is not committed) that line.

 

Cheers,

Jeff