Thread: search_path, schemas and functions...

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

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