[Spacewalk-list] Database Problems "<IDLE> in transaction"

[John Hodrien]

On Wed, 22 Feb 2012, Thomas Ristic wrote:

> Hi,
>
> I see a lot of "<IDLE> in transaction" on my newly installed Spacewalk
> Server (version: 1.6.66, velease: 1.el6).
>
> One of this unfinished transaction seems to lock the table WEB_CONTACT.
> When I try to schedule a package update for multiple systems, the query
> "update WEB_CONTACT set login=$1, login_uc=$2, password=$3, org_id=$4
> where id=$5" hangs forever with waiting status t according to
> pg_stat_activity. When I kill the "<IDLE> in transaction" postgresql
> processes, the action gets scheduled as expected.
>
> I tried to disable osa-dispatcher (5.10.34-1.el6) but this doesn't
> change anything.
>
> postgresql-server is version 8.4.9-1.el6_1.1.x86_64.
>
> Nothing in the logs.
>
> Any suggestions how to fix this?
>
> Thanks for any pointers!

I've had very similar problems and haven't had time to fully get to the bottom
of it.

I have a botch of a cron job that keeps things ticking along:

*  *  *  *  * root /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | /usr/bin/wc \-l ` \-gt 20 && /usr/bin/pkill \-o \-f 'idle in transaction'

It's not pretty, and it's a big pile of wrong, but it's better than not having
it on my setup.

jh