Re: minor annoyance - search_path not reset in/after dump/restore

On Mon, Nov 27, 2017 at 03:04:19PM +0100, Frank van Vugt wrote:
> L.S.
>
> I'm not sure the following qualifies as a 'real' bug, but since it suprised me,
> I'd like to raise it here, just in case it surprises others as well.
>
> Postgresql version 9.6.6
>
> The default value for the search_path is "$user", public.
>
> Since I'm using schema's, my pg_dump files contain lines like this:
> SET search_path = cnt_user, pg_catalog;
> for each particular schema part in the dump.
>
> However, at the end of the dump file, there's nothing to restore the
> search_path to the default / last value.
>
> This means that after restoring a dump via a psql console using a line like:
> \i /tmp/daily-db-backup
> the search_path value will be equal to that of the last schema restored...
>
> This causes unexpected output of commands like '\d' afterwards.
>
> Obviously, disconnecting/reconnecting 'fixes' things.

Interesting. I had not considered someone would include a dump file,
but it makes sense.

The attached patch adds a RESET ALL to the end of the text dump to cause
a reset of all GUC variables. Does this make sense to folks? It would
only be applied to head, so it would only appear in PG 11.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +