Thread: minor annoyance - search_path not reset in/after dump/restore
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. -- Thanks, Frank.
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 +
Attachment
On Tuesday, January 23, 2018, Bruce Momjian <bruce@momjian.us> wrote:
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.
I'm inclined to take the position that if you are going to do something like this you should decide how to proceed when the restore completes. Adding \c or RESET ALL immediately after that \i is straight-forward enough and at least in the \c case you'd get the effects of any ALTER DATABASE SET commands in the dump. Now, usually I would place the \i itself in a file and not type it interactively...
While I cannot imagine anyone depending on the current behavior it does encourage one to reconnect to the loaded database regardless of how the restore happened.
David J.
On Tue, Jan 23, 2018 at 08:45:24PM -0700, David G. Johnston wrote: > On Tuesday, January 23, 2018, Bruce Momjian <bruce@momjian.us> wrote: > > 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. > > > I'm inclined to take the position that if you are going to do something like > this you should decide how to proceed when the restore completes. Adding \c or > RESET ALL immediately after that \i is straight-forward enough and at least in > the \c case you'd get the effects of any ALTER DATABASE SET commands in the > dump. Now, usually I would place the \i itself in a file and not type it > interactively... > > While I cannot imagine anyone depending on the current behavior it does > encourage one to reconnect to the loaded database regardless of how the restore > happened. OK, agreed. A pg_dumpall restore will leave you in a different database. I will assume this item is closed. -- 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 +