Re: function cache effect still happening?

Hi Fernando,

I ran into something similar ... with hard-coded queries in a function
that ends up getting cached. My solution was to store the referenced
table in a variable and, as you suggested, use EXECUTE to dynamically
build up and run the query.

Cheers,
Kevin

Fernando Moreno wrote:
> 2008/5/26 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com
> <mailto:singh(dot)gurjeet(at)gmail(dot)com>>:
>
> On Mon, May 26, 2008 at 9:49 PM, Fernando Moreno
> <azazel(dot)7(at)gmail(dot)com <mailto:azazel(dot)7(at)gmail(dot)com>> wrote:
>
> Hi everyone, a few months ago I was still using Postgresql 8.2
> and had the problem described here:
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 , that
> time I solved it using EXECUTE for all sentences accessing
> temporary tables. Right now I'm using 8.3, the scenario is a
> little different but the problem is the same. I have many
> schemas with the same structure (tables, views and one
> trigger), and two functions in the public schema which insert
> and delete data from them, the INSERT and DELETE sentences are
> hard-coded. Every schema represents a store from the same company.
>
> The idea is that just by changing the search_path value to
> something like "schema1,public", it's possible to execute the
> functions and to process data for any schema (one at a time).
> But the problem is here: through the client app, a user
> invokes one of these functions on a given schema (schema1),
> then requests a "store change", actually setting the
> search_path to use another schema (schema2) and again,
> executes any of the functions that access the schema tables,
> BUT the function seems to be still linked to the first schema,
> so new records are added to the wrong schema and delete
> operations don't find the right record. EXECUTE will save the
> day again, but I'd like to know if this is considered a known
> bug even when it was apparently fixed.
>
>
> I don't think it can be categorized as a bug! This is happening
> because all the DML queries are prepared upon first execution, and
> the plan stores the unique identifiers (OIDs) of the objects and
> not the names of the objects. Upon changing search_path, the
> function cache is not flushed, and hence the query plans are still
> operating on the same objects.
>
> I see two possibilities,
>
> i) Flush function cache (only the query plans, if possible) when
> changing search_path.
> ii) Give users the ability to flush the function cache at will.
>
> I don't think (ii) will have much backing, but (i) does make some
> sense.
>
> Best regards,
> --
> gurjeet[(dot)singh](at)EnterpriseDB(dot)com
> singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>
>
>
> Thanks for your reply. I've been digging the list archive and I think
> EXECUTE is the best workaround, at least better than restarting the
> connection, creating the function again or restarting the server (!!).
> By the way, this flushing-function-cache thing seems to be an almost
> esoteric topic, because I wasn't able to find anything clear, unless
> you were talking about it more as a consequence than an action by itself.
>
> On the other hand, perhaps this problem could have been avoided by
> creating the same function in every schema. That way the function
> cache and query plans would be harmless. Am I right?
>
> Cheers.