Re: Problem with records that disappear.

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.