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

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 and used = 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 for the next
      -- available pin.  If another transaction already updated
      -- used to 1 in between this select and the previous, then we
      -- loop (see ELSE statement).

      select pin into v_pin from pins where id = v_id and used = 0
      for update nowait;

      IF FOUND THEN
        -- Update the PIN.  The used = 0 check is unnecessary,
        -- but better safe than sorry.

        update pins set used = 1 where id = v_id and used = 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...