Thanks for your help, I was getting rather confident this app was to
blame, it just didn't make sense to me that it would show up on an
unrelated table. This gives me more information to contact the developer
with.
W are running 8.3, I have been able to reproduce the problem, it
occurs after an insert has occurred.
Tom Lane wrote:
> David Gardner <david@gardnerit.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
>
--
David Gardner