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
