Skip site navigation (1) Skip section navigation (2)

Re: tracking down idle transactions in pg_locks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Gardner <david(at)gardnerit(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: tracking down idle transactions in pg_locks
Date: 2008-07-18 05:14:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Ridvan Lakas ng Bayan S. BaluyosDate: 2008-07-21 01:30:34
Subject: Re: Stopping a transaction as soon as an error occurs
Previous:From: David GardnerDate: 2008-07-18 05:06:26
Subject: Re: tracking down idle transactions in pg_locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group