Re: Am I locking more than I need to? - Mailing list pgsql-general

On Friday 21 May 2004 06:24 pm, Jeff Davis wrote:
> On Fri, 2004-05-21 at 14:33, Carl E. McMillin wrote:
> > Scenario:
> >
> > SELECT ... WHERE cart_id=X FOR UPDATE
> >
> > IF (NOT FOUND) THEN
> >   BEGIN
> > --Here is where nothing is locked.
> > --No way to guarantee no one else will create a record before we do.
> >   INSERT ...
> >   END;
> > END IF;
>
> Instead, I was thinking more like:
>
> BEGIN
> SELECT ... WHERE cart_id=X FOR UPDATE
> IF (NOT FOUND) THEN
> --Here is where nothing is locked.
> --No way to guarantee no one else will create a record before we do.
>   INSERT ...
> ELSE
>   UPDATE ...
> END IF;
> END;

This is basically what I am doing.  See below for the PL/PGSQL for a
trigger based implimentation.  It effectively SERIALIZEs the one table
in question, any other table perfrom at the normail speed.

Hope it helps!

-miker  (see below)

-----------------------------------------


--
-- Merge on INSERT functionallity for Postgres 7.3+
--
-- miker@purplefrog.com / 5-1-04
--
-- CAVEAT EMPTOR: Uses table locks to avoid concurrency issues,
--           so it WILL slow down heavily loaded tables.
--           This effecivly puts the table into
--           TRANSACTION ISOLATION LEVEL SERIALIZABLE mode.
--

CREATE OR REPLACE FUNCTION add_merge_on_insert (
    TEXT,  -- table name
    TEXT,  -- key column
    TEXT[] -- column list to update on deduplication
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

DECLARE
    tablename    ALIAS FOR $1;
    keycol        ALIAS FOR $2;
    updatecols    ALIAS FOR $3;
    trig        TEXT;
    arraydims    TEXT;

BEGIN
    trig := \'
        CREATE FUNCTION "\' || tablename || \'_merge_on_insert_proc" () RETURNS TRIGGER AS \'\'
        DECLARE
            orig \' || quote_ident(tablename) || \'%ROWTYPE;
        BEGIN
            LOCK TABLE \' || quote_ident(tablename) || \' IN ROW EXCLUSIVE MODE;

            SELECT INTO orig * FROM  \' || quote_ident(tablename) || \' WHERE \' || quote_ident(keycol)    || \' =
NEW.\'|| quote_ident(keycol) || \'; 

            IF NOT FOUND THEN
                RETURN NEW;
            END IF;

            UPDATE \' || quote_ident(tablename) || \' SET \';

    arraydims := array_dims(updatecols);
    FOR i IN 1 .. (substring(arraydims from (position(\':\' in arraydims) + 1 ) for ( position(\']\' in arraydims) -
(position(\':\'in arraydims) + 1 ) )))::INT LOOP 
        trig := trig || quote_ident(updatecols[i]) || \' = COALESCE( NEW.\' || quote_ident(updatecols[i]) || \',
orig.\'|| quote_ident(updatecols[i]) || \'), \'; 
    END LOOP;

    trig := substring( trig from 0 for (character_length(trig) - 1));

    trig := trig || \' WHERE \' || quote_ident(keycol)    || \' = NEW.\' || quote_ident(keycol) || \';
            RETURN NULL;
        END;
        \'\' LANGUAGE \'\'plpgsql\'\';
    \';

    EXECUTE trig;
    EXECUTE \'
        CREATE TRIGGER "\' || tablename || \'_merge_on_insert_trig" BEFORE INSERT
            ON \' || quote_ident(tablename) || \' FOR EACH ROW
            EXECUTE PROCEDURE "\' || tablename || \'_merge_on_insert_proc" ();
    \';

    RETURN \'FUNCTION \' || tablename || \'_merge_on_insert_proc (); TRIGGER \' || tablename ||
\'_merge_on_insert_trig;\';
END;

';

CREATE OR REPLACE FUNCTION remove_merge_on_insert (
    TEXT  -- table name
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

BEGIN
    EXECUTE \'DROP FUNCTION "\' || $1 || \'_merge_on_insert_proc" () CASCADE;\';
    RETURN \'FUNCTION \' || $1 || \'_merge_on_insert_proc (); TRIGGER \' || $1 || \'_merge_on_insert_trig;\';
END;

';