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

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