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

I searched for a long time for a response on how correctly use the database
lock feature to create a job distribution system. Also I hate programs that
pools databases every few seconds. Than I noticed that postgresql have a
notify feature that inform clients when a table change. Beautiful!! why not
to use it?
It also works with db server behind firewalls because connection is
initiated from the client.

I think that the correct method to use is described by tom lane in this mail

http://archives.postgresql.org/pgsql-general/2003-05/msg00351.php

First of this mail I used this method:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=002b01c2d6ca%24ac
ceff60%240100a8c0%40kodunet.ee&rnum=3&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3D
ISO-8859-1%26q%3D%2522select%2Bfor%2Bupdate%2522%2B%2B%252B%2522limit%2B1%25
22%26meta%3Dgroup%253Dcomp.databases.postgresql.*

Every concurrent process listen for a notify on a working table.
A new job arrives on a writer table, and is copied by a trigger on a
working table as described by tom lane and others. Also clients are informed
of table change with a notify, so you have not to poll database.

At this point every clients make a select of a new record, and simply try to
update this record.
I notice that after an update I can query if (atoi(PQcmdTuples(res)) !=0)

And see if it returns a positive value I can process the record.

Now what is the difference between this method

////////////////////////////////////////////////////////////////////////////
My poor method

1 ) select id from readertable where owner = 0 limit 1

2) update readertable set owner = $me where (id = $id and owner=0);

3) if (atoi(PQcmdTuples(res)) !=0) then process record

and method described by tom lane ?
///////////////////////////////////////////////////////////////////

--------------------------------------------------
Tom's Lane method

1. When idle, you try to reserve a job like so:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT job_id, ... FROM job_table
WHERE processor_id = 0 LIMIT 1 FOR UPDATE;

The SELECT has three possible outcomes:

2: One row is returned. You do

UPDATE job_table SET processor_id = $me
WHERE job_id = $jobid;
COMMIT;

and then go about executing the job. When done, delete the row from
job_table and try to get another one.

2b: No row is returned: no jobs are pending. Commit your transaction,
sleep for an appropriate delay period, and try again.

2c: You get a "can't serialize" failure .........

-----------------------------------------------

Multiple concurrent updates on a table can generate a table lock so that
clients must wait for a long time ?
Making only 1 update is the correct solution , but who can explain this ?
And what exactly do select for update?
Suppose now that many jobs are sumbitted in the same time.
Now with the second method different jobs are selected by different clients
with only 1 select ?
Why postgresql does not support update table LIMIT 1 ?