Hi,
> Ah. The reason for that is a bit subtle: constant-folding of immutable
> functions happens in the same pass over the query tree as simplification
> of simplifiable constructs --- including COALESCE. So what's happening
> is that eval_const_expressions, working on the COALESCE construct, fi
On Sat, Dec 22, 2012 at 9:31 AM, Adrian Klaver wrote:
> On 12/21/2012 02:22 PM, Chris Angelico wrote:
>> As I understand it, there are three keywords: VOLATILE, STRICT, and
>> IMMUTABLE. Putting one of those keywords into the declaration flags
>> the function accordingly; if none is given, VOLATIL
On 12/21/2012 02:22 PM, Chris Angelico wrote:
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston wrote:
Chris Angelico wrote, and David dropped the citation (oops!):
By the way, why do you declare your functions as "STRICT IMMUTABLE"
and "STRICT VOLATILE"?
Is this a question about the layout of
On Sat, Dec 22, 2012 at 3:53 AM, David Johnston wrote:
> Chris Angelico wrote, and David dropped the citation (oops!):
>> By the way, why do you declare your functions as "STRICT IMMUTABLE"
>> and "STRICT VOLATILE"?
>
> Is this a question about the layout of the commands spatially?
As I understan
"David Johnston" writes:
>> Indeed, COALESCE will not execute the second sub-select at runtime, but
>> that doesn't particularly matter here. What matters is that "ps3(2)"
>> qualifies to be pre-evaluated (folded to a constant) at plan time.
> Understood (I'm guessing there is no "global" cache
> Something to consider: Since you've told Postgres that your function is
> immutable, it might be remembering the result from the first execution and
> using it in the second. Try restarting the server between the EXPLAIN and
the
> test.
>
Now that you've said this as well (hopefully you are not
David Johnston wrote:
> Understood (I'm guessing there is no "global" cache but simply the
> plan-level cache that gets populated each time?)
>
> However, in the following example the ps3(2) expression should also qualify
> for this "folding" and thus the RAISE NOTICE should also appear during pl
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Friday, December 21, 2012 11:16 AM
> To: David Johnston
> Cc: 'Adrian Klaver'; 'jg'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> &qu
David Johnston wrote:
> I thought that in order to call the Coalesce function the system
> would have to know the value of all parameters. There is no lazy
> instantiation in SQL.
Tom already addressed the main question, but I want to clarify this
point. COALESCE is not a function; it is defined
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
> Sent: Friday, December 21, 2012 10:57 AM
> To: David Johnston
> Cc: 'jg'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> On 12/21/2012 07:49 AM, David
"David Johnston" writes:
> The first case is:
> SELECT COALESCE( (SELECT), (SELECT) ); I am not sure exactly what the
> parentheses surrounding the scalar-sub-SELECTs do (turn them into anonymously
> typed rows?) but if the first scalar-sub-select results in a non-null result
> then the secon
On Sat, Dec 22, 2012 at 2:57 AM, jg wrote:
> Hi,
>
> Interesting idea.
> With VOLATILE, the bug disappears.
> With IMMUTABLE, the EXPLAIN and the execution does not match
> That is a bug. Even if the behavior has to be different in VOLATILE and
> IMMUTABLE, the EXPLAIN and the execution MUST
Hi,
Interesting idea.
With VOLATILE, the bug disappears.
With IMMUTABLE, the EXPLAIN and the execution does not match
That is a bug. Even if the behavior has to be different in VOLATILE and
IMMUTABLE, the EXPLAIN and the execution MUST be coherent.
JG
[postgres@]test=# create or replace fun
On 12/21/2012 07:49 AM, David Johnston wrote:
-Original Message-
In the first case the nested parentheses mean the SELECT statements are run
first and the COALESCE is run on the return results.
The first case is:
SELECT COALESCE( (SELECT), (SELECT) ); I am not sure exactly what th
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@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 ?
On Sat, Dec 22, 2012 at 2:40 AM, jg wrote:
> Thank you for the documentation link, but it does not help me.
The documentation link states that a function with side effects *must*
to be declared VOLATILE (or if you prefer, *not* declared STRICT or
IMMUTABLE). Emitting warnings is a side effect; yo
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Friday, December 21, 2012 10:27 AM
> To: jg
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Coalesce bug ?
>
Hi,
Test done on PostgreSQL 9.2.1
pgb=# create or replace function ps3(a int) returns int as $$ BEGIN
pgb$# RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
pgb$# END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION
pgb=# SELECT ps3(1);
WARNING: Call ps3(1)=1
ps3
-
1
(1 row)
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)) );
expla
On Sat, Dec 22, 2012 at 2:25 AM, David Johnston wrote:
> 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
On 12/21/2012 07:03 AM, jg wrote:
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
Hi,
> In PostgreSQL 9.2, I have the following behavior, and I found it strange.
Sorry the test was with 9.1.6
# psql -V
psql (PostgreSQL) 9.1.6
JG
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pg
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of jg
> Sent: Friday, December 21, 2012 10:04 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Coalesce bug ?
>
> Hi,
>
>
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 IMMUTAB
24 matches
Mail list logo
