Re: Problem with records that disappear. - Mailing list pgsql-general

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.