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(dot)klaver(at)gmail(dot)com