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