Re: Serialization, Locking...implement processing Queue with a table

On Wed, 14 May 2003 04:36:35 -0500, "D. Dante Lorenso"
<dante(at)lorenso(dot)com> wrote:
>> FUNCTION reserve_job
>> BEGIN
>> SELECT and LOCK row_id matching our criteria. (SELECT FOR UPDATE)
>>
>> IF (row_id was found) THEN
>> RESERVE row_id (UPDATE)
>> RETURN (row_id) -- done
>> ELSE
>> Ask "Are you Sure?" there are no rows matching our criteria?
>> IF (certainly no row_id exists) THEN
>> RETURN (0) -- no row ID exists
>> ELSE
>> RETURN reserve_job -- recursive call
>> END IF
>> END IF
>> END

What I had in mind was more like

FUNCTION reserve_job
BEGIN
LOOP
SELECT ... FOR UPDATE;
IF (row_id was found) THEN
RESERVE row_id (UPDATE);
RETURN (row_id); -- done
ELSE
SELECT ...; -- without FOR UPDATE
IF (certainly no row_id exists) THEN
RETURN (0) -- no row ID exists
-- ELSE
-- continue loop
END IF;
END IF;
END LOOP;
END;

>Well, DAMNIT, this doesn't work.
>
>The problem is that this function does not see the COMMITED
>data from other functions. I've updated my code to the following:

You're right :-( I did some more tests and ended up with (note,
however, that I have different names and data types):

CREATE OR REPLACE FUNCTION get_next_job (int) RETURNS int AS '
DECLARE
in_pid ALIAS FOR $1;
my_reserved_id int;
BEGIN
-- Find the ID we wish to reserve and get a lock on that row
SELECT id INTO my_reserved_id
FROM job
WHERE pr = 0
ORDER BY id
LIMIT 1
FOR UPDATE;

-- abort if there are no queued rows
IF NOT FOUND THEN
-- check again ...
SELECT id INTO my_reserved_id
FROM job
WHERE pr = 0
ORDER BY id
LIMIT 1;

IF NOT FOUND THEN
RETURN (-1);
ELSE
RAISE NOTICE ''GOT LOCKED IN RACE [%]'', my_reserved_id;
RETURN (0);
-- retry
END IF;
ELSE
-- now go reserve the record with our processor id
UPDATE job SET pr = in_pid
WHERE id = my_reserved_id;

-- this is the row we reserved...
RETURN (my_reserved_id);
END IF;
END;
' LANGUAGE 'plpgsql';

So the caller has to cooperate a little:

while (...) {
job_id = get_next_job(my_proc_id);
if (job_id < 0) {
sleep(...);
} else if (id == 0) {
/* retry */
} else {
process(job_id);
}
}

Make sure that get_next_job() and process() are not called within the
same transaction.

>'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

BTW, I wouldn't call this function STABLE.

You said in your other message:
|PROS
| - in theory, code SHOULD be entirely contained within a
| single stored procedure (less application coding needed)
| - no locks needed (faster execution?)
|CONS
| - requires extra queries to determine empty queue
| - may starve if a single process continues to grab the same
| row as other processes

Unlikely, unless you have really lots of processes. If two processes
see the same request, only one of them can grab it. This process will
be busy for a while processing the request, while the other process
will immediately retry and grab the next open request.

| - need recursive calls in PL/PGSQL?

Better use a loop (in the caller).

IMHO the biggest PRO is:
+ You can put additional conditions into the WHERE clause (e.g.
queue_no = 42) and processors looking for different kinds of requests
will not block each other.

Servus
Manfred