function cache effect still happening?

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.

Thanks.

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

2008/5/26 Gurjeet Singh <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>
> 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.

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.

On Mon, May 26, 2008 at 3:08 PM, Fernando Moreno <azazel(dot)7(at)gmail(dot)com> wrote:
> 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?

yes. I have done partitioning exactly you have several time in the
past...it's pretty powerful.

You can also force a reconnect when you manipulate search_path. I
personally think adding the same function to every schema is lame and
would prefer reconnect if you can fit it in. Of course, you can also
use dynamic sql everywhere but this also has drawbacks.

There has been some consensus on solving this problem...namely to have
the stored plans to be hashed out over search path (so, if your
database has 10 identical schemas, you could have up to 10 stored
plans for each function). btw, prepared statements are also an
issue.

merlin