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 ?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
