Thank you very much for your answer. I think that I am seeing those self
transaction id locks as "ExclusiveLock"
Would you expect to see an "ExclusiveLock" with a query of type Select (not
Select Update or Update or Insert)?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Thursday, February 23, 2006 10:04 AM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote:
> Would connections to a database require crating an extra ExclusiveLock? We
> have some connections to the database that happen to be "idle in
> transaction" and their pids have a granted "Exclusive Lock" in pg_locks.
I
> cannot discern the tables where the ExclusiveLock is being held because
the
> relation field is blank.
AIUI each backend has an exclusive lock on its own transaction. If
you're idle in transaction you've acquired a lock on your transaction
so other people can wait on you if necessary. That's why there's
nothing in the relation field, because it's not a table lock.
> Moreover, there are other connections to the database coming from the same
> ip address as that of the connection with the ExclusiveLock. Some of the
> pids of these other connections seem to have different kinds of locks
> (AccessShareL0ck) so I am not quite sure why the pids with the
> ExclusiveLock's are necessary.
AccessShareLock is the normal lock you acquire when selecting data.
It's doesn't really do much other than say "I'm using this table, don't
delete it". See the documentation for all the details.
> How could I find out the tables that are being locked when I see an
> "ExclusiveLock" in pg_locks.
It's rare to see exclusive locks on tables except for things like
VACUUM FULL and CLUSTER and other such admin commands...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
