Re: Small PosgreSQL locking function request - with bounty

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