atomic function

On Mon, 15 Aug 2005, Mage wrote:

2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint
"common_adviewnum_adid_site_day_index"

Between your select and your insert someone else inserted a row making the
insert fail.

See this example of how you can update or insert depending on what is in
the table:

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

If you don't have 8.0 so you can do as the example you need to lock the
table so no one else can insert between your select and your insert.

--
/Dennis Bj�rklund

Mage schrieb:

Hello!

What's wrong with this function?

public | common_adviewnum_increase | integer | bigint,
character varying | postgres | plpgsql |
declare
row record;
result int;
begin
select into row viewnum from common_adviewnum where adid = $1 and
site = $2 and day = now()::date;
if found then
result = row.viewnum + 1;
update common_adviewnum set viewnum = result where adid =
$1 and site = $2 and day = now()::date;
else
result = 1;
insert into common_adviewnum (adid, site, day, viewnum)
values ($1, $2, now()::date, result);
end if;
return result;
end;

Every 2-3 day I get this in the server log:

2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint
"common_adviewnum_adid_site_day_index"
CONTEXT: SQL statement "insert into common_adviewnum (adid, site, day,
viewnum) values ( $1 , $2 , now()::date, $3 )"
PL/pgSQL function "common_adviewnum_increase" line 11 at SQL statement
select common_adviewnum_increase(820434,'H');

You might find out by replacing this function with something you find
here:

http://www.postgresql.org/docs/current/static/functions-sequence.html

Dennis Bjorklund wrote:

On Mon, 15 Aug 2005, Mage wrote:

2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint
"common_adviewnum_adid_site_day_index"

Between your select and your insert someone else inserted a row making the
insert fail.

I see. I thought that the function will be atomic but I was wrong.

See this example of how you can update or insert depending on what is in
the table:

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Or should I simply lock the table?

Mage