Re: function cache effect still happening?

On Mon, May 26, 2008 at 9:49 PM, Fernando Moreno <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