> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of jg > Sent: Friday, December 21, 2012 10:04 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Coalesce bug ? > > Hi, > > In PostgreSQL 9.2, I have the following behavior, and I found it strange. > > ps3 is executed or "never executed" ? !!! > > JG > > [postgres@]test=# 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; CREATE FUNCTION Temps : 22,632 > 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,692 ms > [postgres@]test=# select coalesce( ps3(1), ps3(2) ); > WARNING: Call ps3(1)=1 > coalesce > ---------- > 1 > (1 ligne) > > Temps : 0,441 ms > > [postgres@]test=# explain (analyze, verbose, 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.006..0.006 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.001..0.001 > 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.024 ms > (9 lignes) > > Temps : 0,819 ms > You have defined the function as "IMMUTABLE". The system is allowed to cache the results of a given call (i.e. "ps3(2)") and return the value without actually executing the function ("never executed"). Your second example returns "1" without a warning regarding the "2" invocation due to this. The Query Plan you show also matches this behavior. I am curious as to why the Explain Analyze version has both warnings yet indicates that the cache was used. I would ask that you confirm that query plan shown was generated at the same time as the two warnings and that it is not a copy-and-paste/timing error. While unusual the contract of IMMUTABLE does not supposedly preclude this mismatch. However, I have to leave it to more knowledgeable people to confirm, research, and explain this behavior. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
