Hi Brice,
I think You are right, problem is just in php prepare/bindvalue
So it should be avoided...
I guess the reason you like to use bindvalue is safety in SQL injection
problem...
what should be handled on some way what depends on concrete case...
But far as I am aware string as input parametar of an function is safe
enough...
Kind Regards,
Misa
Sent from my Windows Phone
From: Brice Maron
Sent: 20/01/2012 19:28
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Immutable function with bind value
On Fri, Jan 20, 2012 at 16:00, David Johnston <polobo@yahoo.com> wrote:
> On Jan 20, 2012, at 6:15, Brice Maron <bmaron@gmail.com> wrote:
>
>> Hi,
>>
>> i've discovered something kind of weird =C2=A0while developing my app...
>> I was trying to fetch some records in a table using a function immutable.
>> In my interface it was really slow and while i was in a psql it was
>> really fast ...
>>
>> After some research i've found out that it was caused by the bind
>> parameter "forcing" the immutable function to execute each time my
>> query gets a record.
>>
>> while i know that the value can't be known at planning time the
>> difference between the binded / not binded is quite enormous...
>>
>> i've isolated a test case here....
>>
>> https://gist.github.com/e93792540cb3a68054c9
>>
>> (it happens at least in pg 8.4 and 9.1.2)
>>
>>
>> What do you think about it... bug? feature? =C2=A0how can i avoid it?
>>
>>
>> Thanks
>>
>>
>
> While you prove the behavior exists your test case is not a realistic exa=
mple of why you would do such a thing.
>
> I would have to say that I'd expect your query to execute the function on=
ce and cache the result for the remainder of the statement. To that end hav=
e you tried defining it as a STABLE function instead of immutable?
>
> In the real use-case what is it you are trying to accomplish?
>
> You might try using a WITH clause to resolve your function call and then =
use the result in the main query.
>
> David J.
Hi,
i know my code is not a real world thing but i tried to isolate the proble=
m...
Here is another piece that look more real and close to the problem i have...
https://gist.github.com/d83a9c5436d7cb8cebec
the taxonomy table has 300 000 records and the difference between
name_normalized like normalize(?) || '%'
and
name_normalized like normalize('my Taxa') || '%'
is really huge!
Thanks for the help
Brice
--=20
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
