On Nov 30, 2005, at 9:22 PM, Thomas F. O'Connell wrote:
I'm monitoring locks using this query:
SELECT pgsa.procpid, pgsa.current_query, pgsa.query_start,
pgc.relname, pgl.mode, pgl.granted
FROM pg_catalog.pg_class pgc, pg_locks AS pgl, pg_stat_activity AS
pgsa
WHERE pgl.pid = pgsa.procpid
AND current_query <> '<IDLE>'
AND pgl.relation = pgc.oid
ORDER BY pgsa.query_start DESC;
which was built as an extension of this information:
http://archives.postgresql.org/pgsql-novice/2004-08/msg00291.php
Interestingly, I'm seeing evidence that SELECTs are occasionally
taking Row Exclusive locks. Should this surprise me?
PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
Actually, let me clarify/rephrase my question. This query as written
doesn't necessarily seem to help me distinguish clearly between
queries that are waiting for a lock and those that are holding a lock.
What I would expect to see during contentious periods in a given
database would be a core of rows in pg_locks with granted = true and
then a stable of additional rows with granted = false.
For instance, if a long SELECT were running against table_foo and an
UPDATE arrived wanting to update table_foo, I would expect to see in
pg_locks an entry corresponding to the SELECT with granted = true and
an entry corresponding to the UPDATE with granted = false.
In reality, I often see hundreds of rows in pg_locks and am lucky
ever to see granted = false among them. And in the rows that I do
see, I occasionally see a SELECT corresponding to
pg_stat_activity.current_query with the same pid as a granted Row
Exclusive lock in pg_locks.
I feel like I must be missing something in my interpretation, but I'm
not sure what.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
