Version of postgres is 8.2.4.
Maybe it will help to give more b/g on how I'm identifying the problem?
The way this materializes as a real issue surrounds transactions left idle. There is a bug in our app that we haven't tracked down yet where on occasion we end up with connections marked "<IDLE> in transaction". As a stop-gap for now, I wrote a script that does the following:
1) queries postgres to identify those connections
select procpid as age from pg_stat_activity where user <> 'slony' and user <> 'kettle' and current_query = '<IDLE> in transaction' and (now() - query_start) > interval '1 minute'
2) When it finds PIDs that match the criteria, we run some diagnostic queries before killing the PIDs (to help us track down the bug in our app that's the root cause)
a) List of non-idle statements
select *, now() - query_start as age from pg_stat_activity where current_query <> '<IDLE>'
b) List of database locks
SELECT pg_class.relname AS table, pg_database.datname AS database, transaction, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid ORDER BY pg_class.relname, mode
3) It then kills the PIDs and sleeps for 30s before again printing a list of the non-idle statements that are running for more then 1 minute. At this point, I kill those because I presume they are deadlocked. It's in this second report that I always see that UPDATE statement and in the list of locks I see ExclusiveLock granted on the users table for one of the running pids but not the others.
On Nov 6, 2007 3:01 PM, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
Marc <
pgsql-general@mbreslow.net> writes:
> This is the query that I'm running to view locks:
> SELECT pg_class.relname AS table,
> pg_database.datname AS database,
> transaction, pid, mode, granted
> FROM pg_locks, pg_class, pg_database
> WHERE pg_locks.relation = pg_class.oid
> AND pg_locks.database = pg_database.oid
> ORDER BY pg_class.relname, mode
> I'm pretty sure this filters out transactionid lock types because I'm
> joining to pg_database and pg_class. Pls correct me if I'm wrong though.
probably looking at a transient row lock taken by a blocked SELECT FOR
UPDATE. You didn't show exactly what the real problem was, but I'm
wondering if this is foreign-key conflicts in a pre-8.1 PG version.
regards, tom lane