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

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