Hi!
As I said in other messages, I'm planning with a partner of mine to use
inheritance and schemas to separate access and data from several sources
(companies) for a client we have (accounting office).
The idea is:
- 1 base schema with all table definitions plus functions plus views
- 1 schema per client he (our client) have where all tables would be
inherited from the base schema
- Using views and functions declared on base schema to also manipulate
and retrieve data from each schema
(Before starting, I've made a dump available at
http://godoy.homeip.net/~godoy/hydrogen.sql containing basic data for
testing, if you're interested you'd only need to change some usernames for
permissions in there.)
But we're facing some problems and before doing anything more serious we'd
like to know what to expect when:
a) We are with some restricted search_path set (e.g. after "SET search_path TO
schema_1") and we make a "SELECT * FROM base.view". What we're seeing is
that views are tied to the schema where they were created, no matter if
they are or not fully qualified in their definition. Is this correct? I'd
expect views to respect the search_path if they aren't fully qualified
(i.e. if I created them as "SELECT something FROM table" instead of "SELECT
something FROM schema.table").
b) We are seeing a really weird behaviour when we use functions. It appears
that it disregards the search_path from the second run and on. If I SELECT
from a function with the search_path set to, e.g., schema_1, then when I
set it to schema_2 then I'll still see data from schema_1. Note, here,
that even the function being created on the base schema results were
correctly retrieved at first execution. (You can repeat that use the above
dump by connecting, setting the search path to any of three schemas,
selecting from the function, changing to other schema and then selecting
again from the same function -- you'll see the same result --; then, if you
reconnect and do a first select in another schema and change your
search_path you'll see a different result from the previous connection but
it will be the same result for both search_paths.)
Are these expected behaviors? Is there any way to make both views and
functions work with search_path? It would be really great to be able to
define them only once and use them on all "1k" schemas unchanged.
I believe that the behavior with functions is "a bit" dangerous since it
allows leaking information from one schema to another after a context change.
(These tests aren't taking permissions into account yet, so after applying
those this problem could be reduced but...)
TIA,
--
Jorge Godoy <jgodoy@gmail.com>