Re: fetching unique pins in a high-transaction environment... - Mailing list pgsql-sql

I think we've figured out a way to implement the equivalent of a
READPAST hint in a function.

The basic idea is to loop until we find the next available unlocked
row, using the lock_not_available exception to determine if the record
is locked or not.  Our early testing seems to indicate that this
solution will work, but we would love to hear about simpler and more
efficient ways to accomplish this.

Here's a simplified version of the function which illustrates the
principle:

CREATE OR REPLACE FUNCTION "getpin"() RETURNS varchar
as $$
DECLARE v_id integer := 0; v_pin varchar;
BEGIN LOOP   BEGIN     -- Find the first available PIN.     -- Note: we cannot lock down the row here since we need to
be    -- able to store the ID of the pin to implement the READPAST.     select id into v_id from pins where id > v_id
andstatus = 0     order by id limit 1;
 
     -- Exit if there are no PINs available.     IF NOT FOUND THEN       RAISE EXCEPTION 'no pins available';     END
IF;
     -- Lock down the PIN.  If another transaction beat us to it, we     -- trap the error (see below) and loop looking
forthe next     -- available pin.  If another transaction already updated the     -- status to 1 in between this select
andthe previous, then we     -- loop (see ELSE statement).     select pin into v_pin from pins where id = v_id and
status= 0     for update nowait;
 
     IF FOUND THEN       -- Update the PIN.  The status = 0 check is unnecessary,       -- but better safe than sorry.
    update pins set status = 1 where id = v_id and status = 0;
 
       -- I don't think this should ever happen.       IF NOT FOUND THEN           RAISE EXCEPTION 'this should never
happen';      END IF;
 
       RETURN v_pin;     ELSE       -- Somebody snuck in and updated/grabbed the pin.  Loop.     END IF;
   EXCEPTION WHEN lock_not_available THEN     -- Loop looking for the next available unlocked pin.   END; END LOOP;
END;
$$
language plpgsql;

Thanks...