From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'jg'" <jg(at)rilk(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Coalesce bug ? |
Date: | 2012-12-21 15:25:29 |
Message-ID: | 008c01cddf8f$692f3030$3b8d9090$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of jg
> Sent: Friday, December 21, 2012 10:04 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 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(at)]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(at)]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(at)]test=# select coalesce( ps3(1), ps3(2) );
> WARNING: Call ps3(1)=1
> coalesce
> ----------
> 1
> (1 ligne)
>
> Temps : 0,441 ms
>
> [postgres(at)]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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2012-12-21 15:26:31 | Re: Coalesce bug ? |
Previous Message | Denis Papathanasiou | 2012-12-21 15:24:54 | Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results |