On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
All,
I have a stored procedure that I use to manage a queue. I want to
pop an item off the queue to ensure that only one server is
processing the queue item, so inside PGSQL, use SELECT ... FOR
UPDATE to lock the row. Here's how I pop the queue item:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
"public"."reserve_next_tcqueue" (in_hostname varchar, in_status
char, in_new_status char) RETURNS bigint AS
$body$
DECLARE
my_reserved_id BIGINT;
BEGIN
/* find and lock a row with the indicated status */
SELECT tcq_id
INTO my_reserved_id
FROM queue q
WHERE q.status = in_status
ORDER BY tcq_id ASC
LIMIT 1
FOR UPDATE;
/* we didn't find anything matching */
IF NOT FOUND THEN
RETURN 0;
END IF;
/* change the status to the new status */
UPDATE queue SET
status = in_new_status,
ts_start = NOW(),
ts_end = NULL,
hostname = COALESCE(in_hostname, hostname)
WHERE tcq_id = my_reserved_id;
/* send back our reserved ID */
RETURN my_reserved_id;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
INVOKER;
----------------------------------------------------------------------
When my server is under severe load, however, this function begins
to take a long time to execute and I begin to suspect that the FOR
UPDATE lock might be locking the whole table and not just the row.
How do I know if this is the case, how can I optimize this
procedure, and how should I be doing this differently? I'm
guessing stacks and queues would be common patterns handled in the
PostgreSQL community.
Thoughts?
SELECT ... FOR UPDATE should only be locking the rows returned by
your the select statement, in this case the one row. You can check
what locks exist on a table (and their type) with the pg_locks system
view.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
