Thursday, July 17, 2008

[NOVICE] tracking down idle transactions in pg_locks

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


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

No comments: