From: Ravi Chemudugunta <chemuduguntar@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Performance Implications of Using Exceptions
Date: Tue, 1 Apr 2008 13:20:34 +1300
Message-ID: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com> (raw)
Hello,
I have a typical many to many join table, in this instance it is
capturing the multiplicity described as "one person can have many
rooms and one room can have many persons". Further the join expresses
where in the room the person is sitting, a seat number. I am creating
a function to abstract this away, if there is no record with the same
person and room the insert otherwise if it already exists update the
record with the new seat value.
create table person_room (
id serial,
person_id int,
room_id int,
seat varchar(255),
unique (person_id, room_id)
);
-- version 1
create or replace function add_person_to_room(person int, room int, s
varchar(255)) as $$
begin
insert into person_room(person_id, room_id, seat) values (person, room, s);
exception when unique_violation then
update person_room set seat = s where (person_id = person) and
(room_id = room);
end;
$$ language 'plpgsql';
-- version 2
create or replace function add_person_to_room(person int, room int, s
varchar(255)) as $$
declare
i int;
begin
select into i id from person_room where (person_id = person) and
(room_id = room);
if (not found) then
insert into person_room(person_id, room_id, seat) values
(person, room, s);
else
update person_room set seat = s where (person_id = person) and
(room_id = room);
end;
$$ language 'plpgsql';
Which version is faster?
Does the exception mechanism add any overhead?
Which is more cleaner?
-ravi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chemuduguntar@gmail.com
Subject: Re: Performance Implications of Using Exceptions
In-Reply-To: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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