Problem with records that disappear.

[Condor]

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.



--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[John R Pierce]

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.

[John R Pierce]

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...

[Vlad Arkhipov]

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.

[Condor]

> [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 ?

[John R Pierce]

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.

[Condor]

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).

[John R Pierce]

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;

[Condor]

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.