Serialization, Locking...implement processing Queue with a table - Mailing list pgsql-general

I want to implement a processing Queue with records
in a table.  This means that I'd like to have multiple
processors performing operations against a PostgreSQL
database but have those processors only operating on
one row each.  Ideally, I'd have a PL/PGSQL function
that uniquely updates (reserves a row for operation by
a given process or thread) and returns only one row
at a time from a table like this:

    int row_id = reserve_next_row(int processor_id);

I'm tripping all over myself with transaction isolation
modes, transactions, pl/pgsql and basic table locks.  The
behavior I expect is not happening.

How should I go about implementing a synchronized process id
queue that will select one unique row from a table at a time
and make the selection be safe with concurrent accesses?

To get more technical with my attempts, I am using Java/JDBC,
PostgreSQL 7.3.2, PL/PGSQL, and have the following code:

---------- 8< -------------------- 8< -------------------- 8< ----------
CREATE OR REPLACE FUNCTION reserve_next_import (bigint) RETURNS bigint AS'
DECLARE
    processor_id ALIAS FOR $1;
    my_import_id BIGINT;
    my_number INTEGER;
    my_import_state CHAR;
BEGIN
    -- Gotta get a handle on this semaphore before you can get in here
    LOCK TABLE import IN EXCLUSIVE MODE;

    my_import_id := -1;

    -- Find the import ID we wish to reserve and get a lock on that row
    SELECT import_id, import_state INTO my_import_id, my_import_state
    FROM import
    WHERE import_state = ''Q''
    AND import_processor_id IS NULL
    ORDER BY import_id
    LIMIT 1;
    --FOR UPDATE; -- set this, and processes hit the NOT FOUND below

    IF NOT FOUND THEN
        RAISE NOTICE ''No Items left in the Queue.'';
        RETURN (-1);
    END IF;

    -- now go reserve the right to process that record
    UPDATE import SET
        import_processor_id = processor_id,
        import_prc_start = NULL,
        import_prc_end = NULL,
        import_state = ''R''
    WHERE import_id = my_import_id;

    -- return the ID for us to process...
    RETURN (my_import_id);
END;
'LANGUAGE 'plpgsql';
---------- 8< -------------------- 8< -------------------- 8< ----------

Ideally, I could call this function from psql or JDBC
and have it block all access to other processes or threads
by calling 'LOCK TABLE import IN EXCLUSIVE MODE' and make
sure that only one process gets inside the function at a
time (like Java's 'synchronized()' function).

Well, problem is that my psql instances are still seeing
different views of the data and even though one process
reserves an ID and updates the state to 'R', the next process
doesn't see the update (if it has already started the
function as is waiting at the lock) and so it will reserve
the same ID in the SELECT.

I attempted to fix this by using SELECT FOR UPDATE on my
select statement, but problem there is that then the SELECT
from the second process fails and a -1 is returned by my
function.

OK, so you see what I want?  I want a function that locks
all access to a table, reserves an ID and then releases the
lock.  I then want any other processes that are waiting for
that lock to immediately see the updated information as they
process the code inside the function.

If I set TRANSACTION ISOLATION level to SERIALIZED in JDBC, then
calls will fail if another process is inside the function already.
I don't want this either.  I want processes to WAIT at the lock
then get in and successfully get their own ID.

How is this Done?  Anybody have an answer for me?  Am I going
about this the right way?

Much help appreciated!

Dante

D. Dante Lorenso
dante@lorenso.com
972-333-4139