Thread: warnings about invalid "search_path"

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...

Apart from these warning messages, there is not other problem about it and we are actually able to access any of the tables, sequences, indexes, etc from that schema with out explicitly prefixing the schema name to those relations.

I searched postgres mailing list archives for a solution to this problem but the only discussion that i found matching my problem is http://postgresql.1045698.n5.nabble.com/invalid-search-path-complaints-td5615554.html

Could anyone suggest what could be wrong with my setup and how to get past it?

 Hi Samba,

   first: do not worry, it is perfectly normal.

 On Tue, 7 Aug 2012 16:25:14 +0530, Samba <saasira@gmail.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.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

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?

Samba <saasira@gmail.com> writes:
> 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
> ...
> Could anyone suggest what could be wrong with my setup and how to get past
> it?

From the 9.1.4 release notes:

    * Ignore missing schemas during non-interactive assignments of search_path (Tom Lane)

    This re-aligns 9.1's behavior with that of older branches. Previously
    9.1 would throw an error for nonexistent schemas mentioned in
    search_path settings obtained from places such as ALTER DATABASE SET.

            regards, tom lane

Samba wrote:
> 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...
>
> Apart from these warning messages, there is not other problem about it

Maybe the warning is from a different database that does not have such
a schema.

Try to add %d to the log_line_prefix parameter.

Yours,
Laurenz Albe

I now realize that the issue is indeed occurring when users who have "system_data" in their search_path log in to other databases that does not have that schema.

Could someone explain how to "add schema(s) into search_path for a database [not to user/role] irrespective of whichever user logging-in"?

-----------------------------------------------------------------------------------------------------------------------------------

On Tue, Aug 7, 2012 at 7:15 AM, Samba <saasira@gmail.com> wrote:
> Thanks Gabriele for those pointers,
>
> I could now narrow it down to two things:
>
> "system_data" user logging into other databases [one of those may be the
> default 'postgres'] which does not have "system_data" schema
> 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?

You can set  search path for a particular database:

alter database xyz set search_path='abc','xyz';

I'll try that out; I hope this would solve my problem...