Re: Coalesce bug ? - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of jg
> Sent: Friday, December 21, 2012 10:40 AM
> To: Chris Angelico
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> Hi,
>
> Please test this script on a PostgreSQL 9.1.6,
>
> create or replace function ps3(a int) returns int as $$ BEGIN RAISE WARNING
> 'Call ps3(%)=%',$1,$1; RETURN $1::int; END; $$ LANGUAGE plpgsql STRICT
> IMMUTABLE; SELECT ps3(1); SELECT ps3(2); select coalesce( (select ps3(1)),
> (SELECT ps3(2)) ); explain (verbose, analyze, buffers) select coalesce( (select
> ps3(1)), (SELECT ps3(2)) ); select coalesce( ps3(1), ps3(2) );
>
>
> The result will be
>
> [postgres@]test=# create or replace function ps3(a int) returns int as $$
> BEGIN test$# RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int; test$#
> END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION Temps :
> 13,232 ms [postgres@]test=# SELECT ps3(1);
> WARNING:  Call ps3(1)=1
>  ps3
> -----
>    1
> (1 ligne)
>
> Temps : 0,975 ms
> [postgres@]test=# SELECT ps3(2);
> WARNING:  Call ps3(2)=2
>  ps3
> -----
>    2
> (1 ligne)
>
> Temps : 0,473 ms
> [postgres@]test=# select coalesce( (select ps3(1)), (SELECT ps3(2)) );
> WARNING:  Call ps3(1)=1
> WARNING:  Call ps3(2)=2
>  coalesce
> ----------
>         1
> (1 ligne)
>
> Temps : 0,681 ms
> [postgres@]test=# explain (verbose, analyze, buffers) select coalesce(
> (select ps3(1)), (SELECT ps3(2)) );
> WARNING:  Call ps3(1)=1
> WARNING:  Call ps3(2)=2
>                                          QUERY PLAN
>
> --------------------------------------------------------------------------------
> ------------
>  Result  (cost=0.02..0.03 rows=1 width=0) (actual time=0.004..0.004 rows=1
> loops
> =1)
>    Output: COALESCE($0, $1)
>    InitPlan 1 (returns $0)
>      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows
> =1 loops=1)
>            Output: 1
>    InitPlan 2 (returns $1)
>      ->  Result  (cost=0.00..0.01 rows=1 width=0) (never executed)
>            Output: 2
>  Total runtime: 0.022 ms
> (9 lignes)
>
> Temps : 0,774 ms
> [postgres@]test=# select coalesce( ps3(1), ps3(2) );
> WARNING:  Call ps3(1)=1
>  coalesce
> ----------
>         1
> (1 ligne)
>
> Temps : 0,562 ms
> [postgres@]test=#
>
>
> There is a bug !

In words, what behavior in the above do you find "buggy" and what output would you expect to see instead.

The use of "RAISE NOTICE" in an IMMUTABLE function is a grey area since it is arguably a side-effect though a benign
one.

It is not a bug for an IMMUTABLE function to NOT be executed if the result can be known by other means.  It is also not
anerror for an IMMUTABLE function to be executed even if you believe those "other means" should have been used instead.
The presence of IMMUTABLE gives the system a choice of how to proceed - as long as whichever choice it picks does not
changethe semantics of the output.  If you find the "RAISE NOTICE" to be semantically meaningful then you MUST NOT use
IMMUTABLEsince in that case you are explicitly making use of a side-effect. 

>
> Thank you for the documentation link, but it does not help me.
>
> JG