Hello David,
I replied to your original e-mail but it must have vanished into the
ether. I sent you a brief precis about transaction processing.
For "SELECT FOR UPDATE" to function, you MUST have an unique key on the
table. For example:-
crawlq_id SERIAL NOT NULL PRIMARY KEY USING INDEX TABLESPACE xyz,
That creates a sequence and whenever you insert a row into the table, it
automatically grabs the next value and stores it in that column.
Without knowing exactly what or how your application functions, I made a
suggestion that I believe will save you some grief. Create a new table
crawlq_processed (say) and your transaction flow becomes:-
BEGIN;
SELECT row FOR UPDATE;
Supplying the unique key and row is now locked.
Do your processing.
INSERT INTO crawlq_processed;
DELETE FROM crawlq;
COMMIT; or ROLLBACK; if errors occurred.
All of the above in appropriate try . . catch blocks.
You need to set up a cron job to vacuum table crawlq.
The SELECT FOR UPDATE will not stop other processes inserting or reading
from crawlq. You have just locked a single row thus "protecting" it from
being updated or deleted by another process.
HTH.
Cheers,
Robert