Re: BUG #14192: pg_dump/pg_restore omit setting search_path in restored db

On Wed, Jun 15, 2016 at 11:49 AM, Hans Buschmann <buschmann(at)nidsa(dot)net>
wrote:

>
> Thank you for your quick reply (my first post/bug report).
>
> When changeing my database partly to partitions, I introduced two schemas
> to separate current and archive data.
>
> According to Postgres DOC chapter 5.8.3 it is generally not advisable to
> use schema qualified names for any objects but to use search_path instead.
>
> In my opinion this encouraged naming of objects without explicit schema is
> semantically part of the application (e.g. functions) even when not written
> by words.
>
> When setting the search_path altered for the database it becomes
> semantically a part of the database and the application. This implies it
> should be dumped with the content of the database to preserve the
> consistency of the application.
>
> The same applies to cases with only one schema with no standard name (when
> public becomes myapplication).
>
> My point is the logical consistency of what consists a database and how to
> transport all information in one container (a dump).
>
> Even the syntax (ALTER DATABASE xxxdb SET SEARCH PATH) suggests this to be
> part of the database and not of a session or the cluster.
>
> These are my 2 cents as being relatively new to PostgreSQL.
>
I suspect most people would agree with this sentiment. But the status-quo,
while less than ideal, allows for the end result to be realized, if
imperfectly, and thus the motivation to donate ones time to improving it is
not that great.

​The underlying point is that users are global - but there is no "global
shell" to operate from so every command has to be executed while within a
specific database. The specific mapping you are making when you do "ALTER
DATABASE" has an optional user component (which if not specified simply
means all users) and so it too ends up being global​.

David J.