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 03:12:16
Message-ID: 8766.1216350736@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: David GardnerDate: 2008-07-18 05:06:26
Subject: Re: tracking down idle transactions in pg_locks
Previous:From: David GardnerDate: 2008-07-18 01:41:13
Subject: tracking down idle transactions in pg_locks

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