Re: Basic locking question

On Tue, Sep 06, 2005 at 04:25:38PM -0700, Ben wrote:
> So I'm looking for "lock <tablename> in exclusive mode"?

What version of PostgreSQL are you using?  In 8.0 and later a
PL/pgSQL function could trap a unique constraint violation and issue
a SELECT query instead.  If that sounds ugly then I'd say locking
the entire table is even uglier.

Here's a possible solution (only minimally tested):

CREATE FUNCTION getkey(k text) RETURNS integer AS $$
DECLARE
    retval  integer;
BEGIN
    LOOP
        SELECT INTO retval id FROM foo WHERE keyval = k;

        EXIT WHEN FOUND;

        BEGIN
            INSERT INTO foo (keyval) VALUES (k);
            RETURN currval(pg_get_serial_sequence('foo', 'id'));
        EXCEPTION
          WHEN UNIQUE_VIOLATION THEN
              NULL;
        END;
    END LOOP;

    RETURN retval;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

This function should handle race conditions, and it should only
block when multiple transactions try to insert the same key.  If
the key already exists then the expensive exception-handling code
won't be entered.  Alternatively, you could try the INSERT first
and then do the SELECT if the INSERT failed.

--
Michael Fuhr