Re: Problem with records that disappear.

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 ?