pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtuning a function to insert/retrieve values from a reference table
2+ messages / 2 participants
[nested] [flat]
* tuning a function to insert/retrieve values from a reference table
@ 2007-07-10 15:03 Marc Mamin <M.Mamin@intershop.de>
2007-07-10 15:41 ` Re: tuning a function to insert/retrieve values from a reference table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Marc Mamin @ 2007-07-10 15:03 UTC (permalink / raw)
To: pgsql-performance
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: tuning a function to insert/retrieve values from a reference table
2007-07-10 15:03 tuning a function to insert/retrieve values from a reference table Marc Mamin <M.Mamin@intershop.de>
@ 2007-07-10 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-07-10 15:41 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-performance
"Marc Mamin" <M.Mamin@intershop.de> writes:
> Can I optimize this function with:
> a) remove the EXCEPTION clause (Is there an underlying lock that prevent
> concurrent inserts ?)
No.
> b) declare the function being IMMUTABLE ?
Certainly not --- it's got side-effects.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-07-10 15:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-10 15:03 tuning a function to insert/retrieve values from a reference table Marc Mamin <M.Mamin@intershop.de>
2007-07-10 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox