DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=sdrYD897i+GUnidPCcI4b6PDJLX/mDpsf9gOiUJgKGUIJDAdF5ozlXW1rmkzgIoYf4gbahQFjE4+GqpoCwxYpVBDsvHvR0edjko3MqKHsrnyPGpmd2Hnvgolnc5oSGPEVNhrp9LLC9j8BxZ/6Na4D8kqeQ194f/8u4FeC2MO/V0=
Message-ID: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com>
Date: Tue, 1 Apr 2008 13:20:34 +1300
From: "Ravi Chemudugunta" <chemuduguntar@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Performance Implications of Using Exceptions
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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