SELECT Generating Row Exclusive Locks?

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)

"Thomas F. O'Connell" <tfo@sitening.com> writes:

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.

Why would you expect to see that exactly? SELECTs don't block UPDATEs.

regards, tom lane

On Nov 30, 2005, at 10:52 PM, Tom Lane wrote:

"Thomas F. O'Connell" <tfo@sitening.com> writes:

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.

Why would you expect to see that exactly? SELECTs don't block
UPDATEs.

Mm. I must've been projecting my notion of a problem onto one that
wasn't there, reading (and not thinking) Row Exclusive instead of
Access Exclusive for conflicts. Duh.

I guess I'm still somewhat puzzled by the original statement of the
question, then. Why does that particular view of locks occasionally
tie a SELECT to a granted Row Exclusive lock? I recognize that the
pid in pg_locks can be the pid of the server process holding or
awaiting the lock, but I'm seeing granted = true on these, which
implies that the server process corresponding to the SELECT is
holding a Row Exclusive, doesn't it?

--
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)

"Thomas F. O'Connell" <tfo@sitening.com> writes:

I guess I'm still somewhat puzzled by the original statement of the
question, then. Why does that particular view of locks occasionally
tie a SELECT to a granted Row Exclusive lock?

You sure it's not left over from an update command earlier in the
same transaction?

regards, tom lane

On Nov 30, 2005, at 11:24 PM, Tom Lane wrote:

"Thomas F. O'Connell" <tfo@sitening.com> writes:

I guess I'm still somewhat puzzled by the original statement of the
question, then. Why does that particular view of locks occasionally
tie a SELECT to a granted Row Exclusive lock?

You sure it's not left over from an update command earlier in the
same transaction?

Pretty sure, unless the query I posted earlier can cause the display
of leftover commands...

--
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)