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

On Wed, 14 May 2003 04:36:35 -0500, "D. Dante Lorenso"
<dante@lorenso.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