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
