Re: function cache effect still happening? - Mailing list pgsql-general

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.gurjeet@gmail.com
> <mailto:singh.gurjeet@gmail.com>>:
>
>     On Mon, May 26, 2008 at 9:49 PM, Fernando Moreno
>     <azazel.7@gmail.com <mailto:azazel.7@gmail.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[.singh]@EnterpriseDB.com
>     singh.gurjeet@{ 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.