The ExclusiveLock seems to be granted on the transaction id instead of tables. So I am guessing that, for a connection, the first lock is granted to the transaction id and later other locks are granted on specific tables. I am running the following from the console: psql -d emrprod -c "select pg_stat_activity.datname,pg_class.relname,pg_locks. transaction, pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,substr(pg_ stat_activity.current_query,1,30) as "query", pg_stat_activity.query_start, age( now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid from pg_s tat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class. oid) where pg_locks.pid=pg_stat_activity.procpid order by query_start;"|grep -v IDLE Typical outputs are the following: 1) First example emrprod | | 9507777 | ExclusiveLock | t | emruser | SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | mr0011 | | AccessShareLock | t | emruser | SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | sy0001a | | AccessShareLock | t | emruser | SELEC T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 6193 emrprod | mr0050 | | AccessShareLock | t | emruser | SELEC T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737 | 2) Second Example emrprod | | 9509136 | ExclusiveLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0001 | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0001a | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 emrprod | sy0004 | | AccessShareLock | t | emruser | SELEC T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588 | 9667 -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 1:36 PM To: Carlos Oliva Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 01:23:36PM -0500, Carlos Oliva wrote: > Yes. I am seeing that situation often in our database. > > The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or > UPDATE or INSERT or DELETE. I was expecting the query to say something like > SLECT UPDATE or something like that. Also the query seems to have just > columns in the select statement; not functions. > > I will look further into these queries in case that they are using > functions. Are the ExclusiveLock locks for relations or for transaction IDs? Also, once a lock is acquired it's held until the transaction completes, so if the transaction ever acquired that lock then the transaction would still be holding it. If you can't figure out what's happening then it might be useful to see the output of SELECT relation::regclass, * FROM pg_locks; A self-contained test case might also be useful. If you show what commands you're running and what pg_locks output you don't understand, then somebody might be able to explain what's happening. -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
