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 <and...@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
