Re: Selecting Function Volatility Category

Thanks David, very helpful. For the moment we decided to test both ways in
order to build our understanding of performance. FWIW it might well be in
that "97%" bracket that's not worth worrying too much about.

That aside, I am still somewhat confused by the following in create
function documentation *"**IMMUTABLE indicates that the function cannot
modify the database and always returns the same result when given the same
argument values; that is, it does not do database lookups or otherwise use
information not directly present in its argument list**"*. Could a function
read from database and still be counted as immutable?

For example, something like, FUNCTION get_ID(in name varchar) returns int
immutable as $$ select x.id from t_lookup x where x.name = name; $$

Thanks
Dinesh


On Fri, Sep 20, 2019, 12:26 PM David Raymond <[email protected]>
wrote:

> For the first part I will defer to wiser people, but I think you’re stuck
> with defining it as STABLE as there’s still that 1 transaction a month
> where it could break between statements and cause havoc.
>
>
>
> For the second part I would think the gap would only exist if you did the
> DROP FUNCTION and the CREATE FUNCTION in separate transactions.
>
> Doing them in the same transaction, or using either of CREATE OR REPLACE
> FUNCTION, or ALTER FUNCTION should leave no point at all in which the
> function isn’t available to the rest of the users/transactions... Correct?
>
>
>
> Transaction mechanics aside, I think this is the primary line of interest
> from the docs:
>
> “If you drop and then recreate a function, the new function is not the
> same entity as the old; you will have to drop existing rules, views,
> triggers, etc. that refer to the old function. Use CREATE OR REPLACE
> FUNCTION to change a function definition without breaking objects that
> refer to the function. Also, ALTER FUNCTION can be used to change most of
> the auxiliary properties of an existing function.”
>
>
>
>
>
> *From:* Dinesh Somani <[email protected]>
> *Sent:* Friday, September 20, 2019 2:13 PM
> *To:* [email protected]
> *Subject:* Selecting Function Volatility Category
>
>
>
> I have an id lookup function that translates string names into numeric id.
> The names are pretty stable over the life of the system but still can
> mutate once in a while (like, once a month) when someone updates
> configurations. I can use it like...
>
>
>
>   WHERE ... a.attribute_id = f('SALES', 'MATERIAL_NUMBER') ...
>
>
>
> Currently I am classfying the function as STABLE. I would prefer to use
> category IMMUTABLE as that might be more performant. (
> https://www.postgresql.org/docs/current/xfunc-volatility.html) How does
> one tackle the occasional case when the cached values of the function no
> longer apply? Is there some way to trigger cache invalidation?
>
>
>
> I had thought of deleting and recreating the function. But that leaves a
> tiny gap during which the function becomes non-existent (leading to a hit
> on my SLAs), plus may also cause plan invalidations all over the
> application.
>
>
>
> Regards
>
> Dinesh
>
>
>