Re: Basic locking question - Mailing list pgsql-general

From Michael Fuhr
Subject Re: Basic locking question
Date
Msg-id 20050906235132.GA8350@winnie.fuhr.org
Whole thread Raw
In response to Re: Basic locking question  (Ben <bench@silentmedia.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: SLOOOOOOOW
Next
From: Alvaro Herrera
Date:
Subject: Re: fix pg_autovacuum