Re: tracking down idle transactions in pg_locks

David Gardner <david(at)gardnerit(dot)net> writes:
> Tom Lane wrote:
>> 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.

> W are running 8.3, I have been able to reproduce the problem, it
> occurs after an insert has occurred.

Oh ... well that's just bad design :-(. If the app goes to sleep with
an uncommitted insert then it's capable of blocking other transactions,
quite independently of VACUUM. For instance, an attempt to insert a
conflicting unique-key value would have to block.

regards, tom lane