Performance Implications of Using Exceptions

From: "Ravi Chemudugunta" <chemuduguntar(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance Implications of Using Exceptions
Date: 2008-04-01 00:20:34
Message-ID: 7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephen Frost 2008-04-01 00:52:35 Re: Performance Implications of Using Exceptions
Previous Message Greg Smith 2008-03-31 22:44:27 Re: POSIX file updates