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?
Don't think so. The most efficient way is probably to signal the inserting process so it knows to dis/reconnect. The LISTEN/NOTIFY commands might be useful to you here.
The only other option I can think of is to put the schema changes into a "pending" table and have the inserting process responsible for creating the schemas. That way it knows it needs to dis/reconnect.
All-in-all I think your suggested approach is the best idea.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
