On May 23, 2006, at 9:20 PM, Jorge Godoy wrote:
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").
Yes. Views essentially end up with schemas hard-coded into them. If
that doesn't work you should be able to create views on set returning
functions, though that's obviously more work.
I don't know how hard it would be to allow views to become
search_path aware on execution, or if such a change would be accepted.
Ultimately though, why is this a problem? Aren't you defining all the
views in their appropriate schema?
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.)
This is due to query plan caching. If you grab a new connection every
time you switch companies it won't be an issue. There's also been
talk of adding the ability to 'reset' a connection, but I don't
remember the status of that or if it would reset the query plan cache.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
