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(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