Re: atomic function

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