warnings about invalid "search_path"

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

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.Bartolini@2ndquadrant.it> wrote:

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

Hi all,

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"?

Thanks and Regards,
Samba

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

On Tue, Aug 7, 2012 at 7:50 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:

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';

Thanks Scott,

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

Regards,
Samba
============================================================

On Tue, Aug 7, 2012 at 8:30 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote: