Re: search_path, schemas and functions...

Andrew B. Lundgren wrote:
> 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