Hello,
I have a simple table id/value, and a function that returns the id of a given value, inserting the later if not yet present. The probability that a value already exist within the reference table is very high.
Different db users may have their own reference table with different content, but as the table definition is identical, I've defined a public function to maintain these tables.
Can I optimize this function with:
a) remove the EXCEPTION clause (Is there an underlying lock that prevent concurrent inserts ?)
b) declare the function being IMMUTABLE ?
- although it may insert a new raw, the returned id is invariant for a given user
(I don't really understand the holdability ov immutable functions; are the results cached only for the livetime of a prepared statement ?, or can they be shared by different sessions ?)
Thanks,
Marc
--Table definition:
create table ref_table (
id serial NOT NULL,
v varchar NOT NULL,
constraint ref_table_pk primary key (id)
) without oids;
create unique index ref_table_uk on ref_table(v);
-- Function:
CREATE OR REPLACE FUNCTION public.get_or_insert_value("varchar") RETURNS INT AS
$BODY$
DECLARE
id_value INT;
BEGIN
SELECT INTO id_value id FROM ref_table WHERE v = $1;
IF FOUND THEN
RETURN id_value;
ELSE --new value to be inserted
DECLARE
rec record;
BEGIN
FOR rec in INSERT INTO ref_table (v) VALUES ($1) RETURNING id
LOOP
return rec.id;
END LOOP;
EXCEPTION --concurrent access ?
WHEN unique_violation THEN
RETURN(SELECT id FROM ref_table WHERE v = $1);
END;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
