Re: Serialization, Locking...implement processing Queue with a table
От | Manfred Koizar |
---|---|
Тема | Re: Serialization, Locking...implement processing Queue with a table |
Дата | |
Msg-id | r2g4cvsuc5eneemsdfrnjvuhjqal1dtpk2@4ax.com обсуждение исходный текст |
Ответ на | Serialization, Locking...implement processing Queue with a table ("D. Dante Lorenso" <dante@lorenso.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: dump/restore to 7.4devel giving "[archiver (db)] error returned by PQputline"