Re: BUG #8329: UPDATE x SET x.y = x.y + z does not work in PL/pgSQL - Mailing list pgsql-bugs

Hi,

according to the previous thread, can you update the documentation, please:
http://www.postgresql.org/docs/current/static/functions-math.html
so there is clean what results do you get eg. on "NULL + 1" (and "1 +
NULL"), which gives you null and it is different from sum(x), where it
gives 1 where are NULL and 1 in column x. Probably you should introduce
coalesce() here.

Thank you,

Petr



On 29.7.2013 22:55, Petr Chmelar wrote:
> Dear Pavel and Andrew,
>
> avoiding the nulls solves the thing - thank you! I was considering
> NULL as 0... lame.
> I just wonder why it was working in the console, but it is not
> important - at the moment it works just fine.
>
> Cheers,
> Petr
>
> On 27.7.2013 22:59, Pavel Stehule wrote:
>> Hello
>>
>> 2013/7/27 Andrew Gierth <andrew@tao11.riddles.org.uk>:
>>> Seems clearly your mistake to me... you do realize that (null + z) is
>>> always going to be null, right? Maybe your totals columns should have
>>> been declared NOT NULL (and presumably DEFAULT 0) to avoid this
>>> problem?
>>>
>>> Adding some diagnostics to your function (and fixing all the syntax
>>> errors) and running it shows that you're frequently trying to add to
>>> nulls, e.g.:
>>>
>>> NOTICE:  sum_pkt_in_int = <NULL>
>>> NOTICE:  sum_orig_raw_pktcount = 4
>>> NOTICE:  sum_pkt_in_int = <NULL>
>>> NOTICE:  sum_orig_raw_pktcount = 599
>>>
>>> these diagnostics were obtained as follows:
>>>
>>> CREATE OR REPLACE FUNCTION statistics.notice(text, anyelement)
>>>   RETURNS anyelement
>>>   LANGUAGE plpgsql
>>> AS $function$ begin raise notice '% = %', $1, $2; return $2; end;
>>> $function$
>>>
>>> and changing your update to:
>>>
>>>    sum_pkt_in_int  = notice(''sum_pkt_in_int'',sum_pkt_in_int)
>>>                      +
>>> notice(''sum_orig_raw_pktcount'',sum_orig_raw_pktcount),
>>>         -- XXX THIS IS IT, does not work even when ... + 1000000 XXX
>>>
>>> (doing \set VERBOSITY terse in psql is a good idea for this case to
>>> avoid excessive CONTEXT output)
>>>
>>> --
>>> Andrew (irc:RhodiumToad)
>>>
>> it is strange. I didn't find any problem on tested data, although a
>> bugs was displeasing.
>>
>> If query works from console, then planner is clearly ok, and possible
>> issue can be somewhere in plpgsql. But it should be located more
>> preciously.
>>
>> You can use a debug function or debug trigger
>>
>> CREATE OR REPLACE FUNCTION statistics.foo()
>>   RETURNS trigger
>>   LANGUAGE plpgsql
>> AS $function$
>> begin
>>    if new.sum_pkt_in_int is null then
>>      raise notice 'attention, new is null';
>>    end if;
>>    return new;
>> end;
>> $function$
>>
>> create trigger xx before update on hosts1 for each row execute
>> procedure foo();
>>
>> Regards
>>
>> Pavel
>>
>> p.s. check if COALESCE helps, and then problem is somewhere in data
>> probably
>>
>> sum can return null if all values are null
>>
>> postgres=# select sum(a) is null from (values(null::integer)) x(a);
>>   ?column?
>> ----------
>>   t
>> (1 row)
>>
>>
>>
>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>