Re: View definition and schema search path bug or expected behaviour?

On 07/25/2012 07:25 PM, Chris Bartlett wrote:
> I am not sure if this is expected behaviour or a bug.
>
> Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2.
>
> Connect as bob (superuser)
>
> In public schema:
>    create table people (cols...)
>    create view people_view as select * from people
>
> Create schema bob
>    create table bob.people (cols...)
>    create view bob.people_view as select * from people
>    (NB: view references people, not bob.people)
>
> Insert a record into bob.people
>
> Select * from bob.people_view
>    -> Nil result set
>    (expected to return the record from bob.people)
>
> Check definition of bob.people_view
>    -> "create view bob.people_view as select * from public.people"
>    (NB: "from public.people" - compare "create view bob.people_view as
> select * from people" above)
>
> I had hoped/expected that a view would use the search path to find the
> table it references. Why does bob.people_view reference public.people?
> Is this a bug or expected behaviour? Do view definitions require
> explicit reference to schema.table? My use case is that I effectively
> want to define a default schema to be replicated for new tenants in a
> multi-tenant system.

http://www.postgresql.org/docs/9.2/static/runtime-config-client.html
"
When objects are created without specifying a particular target schema,
they will be placed in the first valid schema named in search_path. An
error is reported if the search path is empty.
"

I am guessing if you do  show search_path; from psql you will see that
the public schema is before the bob schema. The SELECT for the
unqualified people table in CREATE VIEW bob.people_view will find
public.people first in that case.




--
Adrian Klaver
adrian.klaver@gmail.com