Performance Implications of Using Exceptions

Поиск
Список
Период
Сортировка
От Ravi Chemudugunta
Тема Performance Implications of Using Exceptions
Дата
Msg-id 7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com
обсуждение исходный текст
Ответы Re: Performance Implications of Using Exceptions
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Greg Smith
Дата:
Сообщение: Re: POSIX file updates
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: Performance Implications of Using Exceptions