Обсуждение: BUG #8329: UPDATE x SET x.y = x.y + z does not work in PL/pgSQL
The following bug has been logged on the website: Bug reference: 8329 Logged by: Petr Email address: chmelarp@fit.vutbr.cz PostgreSQL version: 9.2.4 Operating system: Fedora x64 Description: Hi there, we have an issue - if I run a query from a console, it runs OK. When it is run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest version of the query is like this: UPDATE x SET x.y = x.y + z. FROM (SELECT z) as Z WHERE ... ; If I don't use the "+" in the SET command, it works OK. The whle query is quite complex, but OK in a console. If you need the query an tables..., mail me, please. I can't make it public.
On Wed, Jul 24, 2013 at 7:45 AM, <chmelarp@fit.vutbr.cz> wrote: > The following bug has been logged on the website: > > Bug reference: 8329 > Logged by: Petr > Email address: chmelarp@fit.vutbr.cz > PostgreSQL version: 9.2.4 > Operating system: Fedora x64 > Description: > > Hi there, > we have an issue - if I run a query from a console, it runs OK. When it is > run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest > version of the query is like this: > UPDATE x > SET x.y = x.y + z. > FROM (SELECT z) as Z > WHERE ... ; > > > If I don't use the "+" in the SET command, it works OK. The whle query is > quite complex, but OK in a console. If you need the query an tables..., > mail > me, please. I can't make it public. > Can you reduce it to a simpler test case without the details you are not able to show? Statistically it is more likely there is an error in your plpgsql function than in postgres itself. Also, is the "z." a typo, or is there really a dot after the "z"?
Hello 2013/7/24 <chmelarp@fit.vutbr.cz>: > The following bug has been logged on the website: > > Bug reference: 8329 > Logged by: Petr > Email address: chmelarp@fit.vutbr.cz > PostgreSQL version: 9.2.4 > Operating system: Fedora x64 > Description: > > Hi there, > we have an issue - if I run a query from a console, it runs OK. When it is > run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest > version of the query is like this: > UPDATE x > SET x.y = x.y + z. > FROM (SELECT z) as Z > WHERE ... ; > > > If I don't use the "+" in the SET command, it works OK. The whle query is > quite complex, but OK in a console. If you need the query an tables..., mail > me, please. I can't make it public. > It looks like some problem with naming collision - have you some plpgsql variable named "z" ?? Please, you have to send a complete example in this case. Usually this query is ook - and probably you have a problem with some interaction PL/pgSQL | SQL Regards Pavel Stehule > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
Dear Briklen and Pavel, Yes, the z. is a typo, it should be z.z ... I can send the complete query= and tables tomorrow, also I'll try to make it simpler (the function is=20= about 300 lines). Thanks for the early answers, Petr Dne 24.07.2013 17:14 napsal u=C5=BEivatel bricklen: On Wed, Jul 24, 2013 at 7:45 AM, <chmelarp@fit.vutbr.cz> wrote: The following bug has been logged on the website: Bug reference: 8329 Logged by: Petr Email address: chmelarp@fit.vutbr.cz PostgreSQL version: 9.2.4 Operating system: Fedora x64 Description: Hi there, we have an issue - if I run a query from a console, it runs OK. When it=20= is run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest version of the query is like this: UPDATE x SET x.y =3D x.y + z. FROM (SELECT z) as Z WHERE ... ; If I don't use the "+" in the SET command, it works OK. The whle query is quite complex, but OK in a console. If you need the query an tables...,=20= mail me, please. I can't make it public. Can you reduce it to a simpler test case without the details you are not= able to show? Statistically it is more likely there is an error in your= plpgsql function than in postgres itself. Also, is the "z." a typo, or is there really a dot after the "z"?
Dear Briklen and Pavel,
herewith I attached the weird function and tables dumped, I tried to simplify it. See the
XXX THIS IS IT, does not work even when ... + 1000000 XXX
comment in the function, please.
I hope you can reproduce the bug. I also reinstalled the database and made some other things, but the function seems not working. I still hope this is my mistake but it works for me in the console... :(
Cheers,
Petr
On 24.7.2013 21:40, Petr Chmelar wrote:
herewith I attached the weird function and tables dumped, I tried to simplify it. See the
XXX THIS IS IT, does not work even when ... + 1000000 XXX
comment in the function, please.
I hope you can reproduce the bug. I also reinstalled the database and made some other things, but the function seems not working. I still hope this is my mistake but it works for me in the console... :(
Cheers,
Petr
On 24.7.2013 21:40, Petr Chmelar wrote:
Dear Briklen and Pavel,
Yes, the z. is a typo, it should be z.z ... I can send the complete query and tables tomorrow, also I'll try to make it simpler (the function is about 300 lines).
Thanks for the early answers,
Petr
Dne 24.07.2013 17:14 napsal uživatel bricklen:
On Wed, Jul 24, 2013 at 7:45 AM, <chmelarp@fit.vutbr.cz> wrote:The following bug has been logged on the website:
Bug reference: 8329
Logged by: Petr
Email address: chmelarp@fit.vutbr.cz
PostgreSQL version: 9.2.4
Operating system: Fedora x64
Description:
Hi there,
we have an issue - if I run a query from a console, it runs OK. When it is
run using EXECUTE in an PL/pgSQL function, it sets NULL. The simplest
version of the query is like this:
UPDATE x
SET x.y = x.y + z.
FROM (SELECT z) as Z
WHERE ... ;
If I don't use the "+" in the SET command, it works OK. The whle query is
quite complex, but OK in a console. If you need the query an tables..., mail
me, please. I can't make it public.Can you reduce it to a simpler test case without the details you are not able to show? Statistically it is more likely there is an error in your plpgsql function than in postgres itself.Also, is the "z." a typo, or is there really a dot after the "z"?
Вложения
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)
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
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
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. 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 >
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