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

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(at)tao11(dot)riddles(dot)org(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>