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

tracking down idle transactions in pg_locks

From: David Gardner <david(at)gardnerit(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: tracking down idle transactions in pg_locks
Date: 2008-07-18 01:41:13
Message-ID: 487FF4B9.9000306@gardnerit.net (view raw or flat)
Thread:
Lists: pgsql-novice
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?

I did a select on the pg_locks table and filtered by the pid of the idle 
transaction. There were 8 locks, 7 were on tables that the app uses 
however the 8th was of locktype=virtualxid and mode=ExclusiveLock. Could 
this be some sort of global lock?

I have been able to verify that closing the app, does allow us to vacuum 
the table.

-- 
David Gardner


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2008-07-18 03:12:16
Subject: Re: tracking down idle transactions in pg_locks
Previous:From: richard terryDate: 2008-07-18 00:58:38
Subject: Re: updating table where one date field is null

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