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

> ---------- 8< -------------------- 8< ----------
> 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
> I am going to try to implement your suggestion and see
> what I get in comparison to what I am seeing now with my
> LOCK/WAIT code.

Well, DAMNIT, this doesn't work.  The problem goes back
to whatever the heck is happening inside the PL/PGSQL
function?!

The problem is that this function does not see the COMMITED
data from other functions.  I've updated my code to the following:

---------- 8< -------------------- 8< -------------------- 8< ----------
CREATE OR REPLACE FUNCTION "public"."subs_get_next_queued" (bigint) RETURNS
bigint AS'
DECLARE
    in_pid ALIAS FOR $1;
    my_reserved_id BIGINT;
BEGIN
    -- initialize the id
    my_reserved_id := -1;

    -- Find the ID we wish to reserve and get a lock on that row
    SELECT subs_id INTO my_reserved_id
    FROM subscription
    WHERE subs_start_bill_date <= now()
    AND subs_next_bill_date <= now()
    AND subs_processor_id IS NULL
    ORDER BY subs_id ASC
    LIMIT 1
    FOR UPDATE;

    -- abort if there are no queued rows
    IF NOT FOUND THEN
        -- check again to see if there is something I need to process...
        SELECT subs_id INTO my_reserved_id
        FROM subscription
        WHERE subs_start_bill_date <= now()
        AND subs_next_bill_date <= now()
        AND subs_processor_id IS NULL
        ORDER BY subs_id ASC
        LIMIT 1;

        IF NOT FOUND THEN
            RETURN (-1);
        ELSE
            RAISE NOTICE ''GOT LOCKED IN RACE [%]'', my_reserved_id;
            SELECT subs_get_next_queued(in_pid) INTO my_reserved_id;
            RETURN (my_reserved_id);
        END IF;
    END IF;

    -- now go reserve the record with our processor id
    UPDATE subscription SET
        subs_processor_id = in_pid
    WHERE subs_id = my_reserved_id;

    -- this is the row we reserved...
    RETURN (my_reserved_id);
END;
'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
---------- 8< -------------------- 8< -------------------- 8< ----------

Here is what is happening... Process one is started and is humming along
just fine.  Then, I kick process 2 into gear and as soon as a collision
occurs, process 1 spins out of control and goes nuts in an infinite loop.
Apparently, it does not see the changes of process 2 even though they have
already been committed.

PROCESS 1                                  PROCESS 2
 subs_get_next_queued
----------------------
                  126
(1 row)

 subs_get_next_queued
----------------------
                  129
(1 row)

                                            subs_get_next_queued
                                           ----------------------
                                                             140
                                           (1 row)

NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
NOTICE:  GOT LOCKED IN RACE [140]
...
(continues forever and takes down
the PostgreSQL server.  OUCH!)
                                            subs_get_next_queued
                                           ----------------------
                                                             176
                                           (1 row)

                                            subs_get_next_queued
                                           ----------------------
                                                             182
                                           (1 row)

                                           (Continues until PostgreSQL dies
                                           because of other processes
doings)


Can someone please explain what is happening to the PL/PGSQL function
in regards to commit visibility and transactions?

Dante

D. Dante Lorenso
dante@lorenso.com
972-333-4139