idle in transaction - Mailing list pgsql-general

From Rich Ryan
Subject idle in transaction
Date
Msg-id 010201c17fde$d01d8f70$1a02a8c0@gateway
Whole thread Raw
In response to Logging/Debugging  (Tielman J de Villiers <tjdevil@bondnet.co.za>)
Responses Re: idle in transaction
Re: idle in transaction
List pgsql-general
About 25% of the time while running my nightly maintenance script, which
deletes and the copies in a large amount of new data, then vacuums and
recreates indexes, the vacuum command will hang. When I wake up in the
morning, everything is hosed and I have 6000 complaint emails. boohoo. :)
This behavior started a few weeks ago when I upgraded from 7.0 to 7.1.3.
Looking at the process list (ps -x on Readhat Linux 6.2) shows the
following,
 4168 ?        S      1:11 postgres: postgres ucc 207.132.206.57 idle in
transaction
 5754 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5755 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5756 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5757 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5758 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5759 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5791 ?        S      0:01 postgres: postgres ucc 207.132.206.57 VACUUM
waiting
 5794 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5796 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5797 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5798 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5799 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5800 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5801 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5802 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
 5803 ?        S      0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
etc...
If I kill pid 4168, the idle in transaction process, then the vacuum and all
the other waiting processes will proceed fine. Any clues to what could be
hanging or how I could get more information from postmaster about what
transaction is hanging?
Thanks,
Rich





pgsql-general by date:

Previous
From: Tielman J de Villiers
Date:
Subject: Logging/Debugging
Next
From: "Rich Ryan"
Date:
Subject: Vacuum errors and warnings