Re: Exclusive Locks Taken on User Tables?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marc <pgsql-general(at)mbreslow(dot)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.
>
> It won't filter out row-level locks on rows within tables. You're
> 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
>