On 2013-01-14 10:53, Vlad Arkhipov wrote:
> On 01/14/2013 05:15 PM, Condor wrote:
>
>> Hello,
>>
>> from some time I have a very strange problem with my postgresql
>> 9.2.2 64bit.
>> I make a few changes with an plp function:
>>
>> BEGIN
>> UPDATE table SET X = X where id = aid;
>> UPDATE table_2 SET Y=Y where id = aid;
>> IF aid > 0 THEN
>> SELECT INTO ids id FROM table_3 WHERE x = x;
>> IF aid IS NULL THEN
>> INSERT INTO table_3 (id) VALUES (x);
>> ELSE
>> UPDATE table_3 SET id = id + 1 WHERE x = X;
>> END IF;
>> END IF;
>> RETURN 200;
>> END;
>
>> This problem happened one or two times per month
>
> It's very likely that the main problem of your code is a race
> condition inside IF clause. Even if "IF aid IS NULL" condition is
> false, somebody can delete the record before the UPDATE of table_3.
> There is an example of what you trying to do in the documentation:
>
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
> [1].
>
> Links:
> ------
> [1]
>
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
Is that possible to be done without any errors ?
