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