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@momjian.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 +