Re: warnings about invalid "search_path"

Thanks Gabriele for those pointers,

I could now narrow it down to two things:

1. "system_data" user logging into other databases [one of those may be
the default 'postgres'] which does not have "system_data" schema
2. other users [like 'postgres'] logging into their own or even other
databases which does not have "system_data" schema.

I did notice that we have added "system_data" schema to a few other users
[roles] who are also supposed to login to the database containing
"system_data" schema and that is causing this side-effect of logging these
warning messages when users who have "system_data" in the search_path log
into other databases that do not have "system_data" schema.

So, what i understand is needed for me is "how to add a schema (or multiple
schemas) in the search path for a database irrespective of whichever user
logs in to the database?"

Could you explain how to add "schema(s) into search_path for a database
irrespective of the user logging-in?

Thanks and Regards,
Samba

===================================================================================

On Tue, Aug 7, 2012 at 4:53 PM, Gabriele Bartolini <
Gabriele(dot)Bartolini(at)2ndquadrant(dot)it> wrote:

> Hi Samba,
>
> first: do not worry, it is perfectly normal.
>
>
> On Tue, 7 Aug 2012 16:25:14 +0530, Samba <saasira(at)gmail(dot)com> wrote:
>
>> Hi all,
>> I'm seeing some weired errors in the postgres logs after upgrading to
>> postgres-9.1(.3) about the schema added by default to search patch
>>
>> WARNING: invalid value for parameter "search_path": "system_data"
>> DETAIL: schema "system_data" does not exist
>>
>> We do have a user named "system_data" and a schema with the same
>> name...
>>
>
> A schema is something that belongs to a database.
>
> You have just set the search_path for a specific user ("system_data") to
> include "system_data" (am I right?). However, a user can theoretically
> connect to any database on the instance.
>
> The error above is generated when you connect with that user
> ("system_data") to a database that does not have the "system_data" schema.
>
> Please let me have more information if my assumption were wrong.
>
> Cheers,
> Gabriele
>
> Tip/Note: by default, search_path is set to search in the "$user" and
> public schemas. Therefore, if you connect using the "system_data" user, you
> do not need to force searching in that schema.
>
> --
> Gabriele Bartolini - 2ndQuadrant Italia
> PostgreSQL Training, Services and Support
> Gabriele(dot)Bartolini(at)**2ndQuadrant(dot)it - www.2ndQuadrant.it
>