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(at)lorenso(dot)com
972-333-4139