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+
--
-- [EMAIL PROTECTED] / 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;
';
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
