Re: tracking down idle transactions in pg_locks

David Gardner <david(at)gardnerit(dot)net> writes:
> I've been tracking down a problem with idle transactions being left open
> and not being able to vacuum one of my tables. I think I have tracked it
> down to proprietary app, the problem seems to be that it leaves "idle
> transactions" open.

> However the table that we are having problems vacuuming (probably just
> because it is the most active table) is in the same db, but a different
> scheme not accessed by that app. Could this app still be causing the
> problem?

Different schema won't help --- VACUUM assumes that *any* other
transaction in the same database might potentially be able to look at
the target table later, so it can't remove rows that that transaction
might be able to "see".

The most common cause of this problem is apps that issue "COMMIT; BEGIN"
and then go to sleep. If that's what this app is doing (if you don't
know, turn on statement logging and find out...) then updating to a more
recent PG version might help. In 8.3 a transaction doesn't block VACUUM's
row reclamation until it's done something more than just BEGIN.

regards, tom lane