Обсуждение: Problem with records that disappear.
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;
When I call the function from php everything it's seems to work,
but some time modify records just disappear. I don't have any ideas for
the moment
what is the problem in my postgresql or in my php code. I'm sure there
was a recording was made.
I run in middle night vacuum, reindex on tables and vacuum analyze;
I check my postgresql logs, but no any errors. I check the php logs,
but no errors.
My vacuum also did not log any error messages. This problem happened
one or two times per month
and I can't track him. Is not happened very often. My question is: Is
there any chance this function
to stay opened or some thing like that and on the night when vacuum is
started to rollback changes ?
Also any other suggestions are welcome.
Cheers,
Hristo C.
On 1/14/2013 12:15 AM, Condor wrote: > > When I call the function from php everything it's seems to work, > but some time modify records just disappear. I don't have any ideas > for the moment > what is the problem in my postgresql or in my php code. I'm sure there > was a recording was made. > I run in middle night vacuum, reindex on tables and vacuum analyze; > I check my postgresql logs, but no any errors. I check the php logs, > but no errors. > My vacuum also did not log any error messages. This problem happened > one or two times per month > and I can't track him. Is not happened very often. My question is: Is > there any chance this function > to stay opened or some thing like that and on the night when vacuum is > started to rollback changes ? > Also any other suggestions are welcome. are you calling these functions within the context of a larger transaction, or just as standalone statements without an epxlicit BEGIN TRANSACTION ? if they are being called from within a transaction, and something else in that transaction triggers a rollback, then ALL of the changes made in that transaction go away. once a transaction is committed, nothing can undo it, other than restoring a backup or changing the data explicitly in another transaction, or something.
On 1/14/2013 12:45 AM, John R Pierce wrote: > On 1/14/2013 12:15 AM, Condor wrote: >> >> When I call the function from php everything it's seems to work, >> but some time modify records just disappear. I don't have any ideas >> for the moment >> what is the problem in my postgresql or in my php code. I'm sure >> there was a recording was made. >> I run in middle night vacuum, reindex on tables and vacuum analyze; ... oh, and vacuum is totally safe to be run concurrently with transactions, the only negative impact is the performance impact of the additional IO and CPU resources the VACUUM uses while active. Ditto REINDEX, it should be safe, at most it will lock an index til that index is rebuilt.. with autovacuum, doing nightly vacuums and analyzes shouldn't be neccessary...
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.
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 ?
On 1/14/2013 12:15 AM, Condor wrote: > IF aid > 0 THEN > SELECT INTO ids id FROM table_3 WHERE x = x; > IF aid IS NULL THEN > .... oh, I see a logical error here too... if "aid" /is/ in fact NULL then that inner code will never be executed, because NULL can not be > 0, it has no value. For that matter, neither will either of the UPDATE's in front, since id = NULL is never true.
On 2013-01-14 10:45, John R Pierce wrote: > On 1/14/2013 12:15 AM, Condor wrote: >> >> When I call the function from php everything it's seems to work, >> but some time modify records just disappear. I don't have any ideas >> for the moment >> what is the problem in my postgresql or in my php code. I'm sure >> there was a recording was made. >> I run in middle night vacuum, reindex on tables and vacuum analyze; >> I check my postgresql logs, but no any errors. I check the php logs, >> but no errors. >> My vacuum also did not log any error messages. This problem happened >> one or two times per month >> and I can't track him. Is not happened very often. My question is: >> Is there any chance this function >> to stay opened or some thing like that and on the night when vacuum >> is started to rollback changes ? >> Also any other suggestions are welcome. > > are you calling these functions within the context of a larger > transaction, or just as standalone statements without an epxlicit > BEGIN TRANSACTION ? if they are being called from within a > transaction, and something else in that transaction triggers a > rollback, then ALL of the changes made in that transaction go away. > once a transaction is committed, nothing can undo it, other than > restoring a backup or changing the data explicitly in another > transaction, or something. It's a standalone statements without BEGIN TRANSACTION. An update is easy to explain with another but insert with missing row and there is no DELETE command in whole code and no one other have access to server. No any error logs, only access log system insert that user make the changes (no info what changes).
On 1/14/2013 1:34 AM, Condor wrote: > It's a standalone statements without BEGIN TRANSACTION. An update is > easy to explain with another > but insert with missing row and there is no DELETE command in whole > code and no one other have access to server. > No any error logs, only access log system insert that user make the > changes (no info what changes). as I see it, that INSERT can't ever happen. if AID is NULL, then the first IF AID > 0 will be FALSE, so the 2nd IF AID IS NULL will never get evaluated. the two UPDATE's up front will not happen either if AID is null. and, what is SET X=X about?!? thats a big noop anyways. is this 'redacted' code that's been sanitized? the more I look at it, the more I'm cringing. WHERE x=x on that SELECT INTO ids will return the whole table, since X = X is always true unless X is NULL. > 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;
On 2013-01-14 11:41, John R Pierce wrote: > On 1/14/2013 1:34 AM, Condor wrote: >> It's a standalone statements without BEGIN TRANSACTION. An update is >> easy to explain with another >> but insert with missing row and there is no DELETE command in whole >> code and no one other have access to server. >> No any error logs, only access log system insert that user make the >> changes (no info what changes). > > > as I see it, that INSERT can't ever happen. if AID is NULL, then the > first IF AID > 0 will be FALSE, so the 2nd IF AID IS NULL will never > get evaluated. the two UPDATE's up front will not happen either if > AID is null. > > and, what is SET X=X about?!? thats a big noop anyways. > > is this 'redacted' code that's been sanitized? the more I look at > it, the more I'm cringing. WHERE x=x on that SELECT INTO ids will > return the whole table, since X = X is always true unless X is NULL. > > >> 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; Everything after the IF aid > 0 THEN is log statistic information and is not important. The first two updates are important, they actually make user changes: UPDATE table SET X = X where id = aid; UPDATE table_2 SET Y = Y where id = aid; Everything after them is just statics and is not important. I really change this line IF aid IS NULL THEN, it's should be IF ids IS NULL THEN meaning if no record for ids in table_3 where x = X; My mistake.