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

Adrian Klaver <adrian.klaver@xxxxxxxxx> · Wed, 25 Jul 2012 19:37:56 -0700

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@xxxxxxxxx

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general