Обсуждение: atomic function

       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');

       Mage

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