I have a large amount of data that I am managing using schemas. The previous owner of the DB wrote functions to automatically create new schemas on demand, and sets of funtions to interact with them.
Rather than maintain a set of functions that are dynamically re-written with every new schema I wanted to just use the USER search path and update it with each new schema creation. The problem I am running into is I have a single process inserting data using database functions. When I create the new schema and update the search path, the inserter continues inserting into previous search path. I tried writing all of the functions making use of a dynamic string for the function contents and an EXECUTE. This worked, the inserter automatically started inserting data into the correct table. Unfortunately it caused a massive performance hit. For the moment, I have the inserter set to close its connection and re-establish it after a block of inserts. This is not really ideal either as the new schema creation happens only once a day and the batches complete in about 1-2 seconds. Is there a way to cause the functions to re-evaluate on demand to use the new search path without disconnecting? I even manually set the search path each pass, but it didn't work. (probably because the functions were already cached at that point) The only other thing I have come up with is to set a flag when I put in a new schema that would cause the inserter to disconnect, reconnect, clear the flag and continue until the flag changes back. Is there a better way to do this that I am missing? Thanks -- Andrew Lundgren ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match