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

Hello

2013/8/11 Petr Chmelar <chmelarp@fit.vutbr.cz>:
> 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.

These information is general - and it is related to ANSI SQL NULL definition.

But I didn't find it in PostgreSQL documentation in one place.

Please, can you send a patch

Regards

Pavel


>
> 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
>>
>>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs