Re: Problem with records that disappear.

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.