Re: ExclusiveLock without a relation in pg_locks - Mailing list pgsql-general

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Thursday, February 23, 2006 1:36 PM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
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@postgresql.org so that your
       message can get through to the mailing list cleanly