Re: What to expect when mixing inherited tables and different schemas while dealing with functions and views?

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461