Reset search_path to the global, cluster default

Say I permanently alter my database's search_path like this:

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema; 

How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?

This is usually "$user",public, but that can be changed from the configuration file. Having a way to read the value of parameters that come from the configuration file would work (so I could build a dynamic query at worst), but I haven't had any luck finding anything of the sort.

Replay

Factory defaults

How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?

You can read boot_val from pg_settings, but that's actually the compiled-in factory default, not the setting in postgresql.conf.

SELECT boot_val
FROM   pg_settings
WHERE  name LIKE 'search_path';

You aren't overlooking the simple RESET?

To just reset the setting (remove it), you can simply:

ALTER DATABASE my_db RESET search_path;

But that doesn't necessarily give you what "a pristine, unaltered database on this cluster would have". It just removes the custom setting for the database. There are several ways to set the search path:

And it does not change the search_path of your current session. The effect (if any) is visible in your next session.

Connect to template1

You could get the current setting from a connection to template1, like @Abelisto suggested. But you probably want to stay within your current session. You could use dblink for that:

SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
                         , $$SELECT boot_val, source FROM pg_settings
                             WHERE name LIKE 'search_path'$$
                          ) AS t(search_path text, source text);

search_path    | source
---------------+---------
"$user",public | default

This has a couple of flaws:

  • You need the additional module dblink installed. That's simple:
    • Persistent inserts in a UDF even if the function aborts
  • You need the privilege to connect to template1. And you need to supply the user pw.
  • Even in template1 the setting might stem from settings to the role or database (or another instance) as well (even though they probably don't). To make sure check the columns source. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...

Read actual setting in postgresql.conf

.. usingpg_read_file(), but this is also subject to some important restrictions. The manual:

Use of these functions is restricted to superusers.

And:

Only files within the database cluster directory and the log_directory can be accessed.

Bold emphasis mine.
If your config files are somewhere else (like in default installations on Debian and friends) you would have to create a symbolic link in the file system in your data or log directory to your actual config file.

SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM   pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
                  , 0, 10000000) AS txt;  -- arbitrary 10 MB max.

search_path
------------
public

Note how I fetch the path to the config file dynamically with:

SELECT setting FROM pg_settings WHERE name = 'config_file'

Then I use a regular expression with substring() to extract the setting:

\nsearch_path\s*=\s*'([^']+)'

That should work, I didn't spend much time to make the regexp bullet-proof, though. Might be fooled by a commented setting that matches first or something. You might want to test some more.

Finally, since Postgres 9.4, there is also the ALTER SYSTEM command to override settings in a postgresql.auto.conf file. You'll have to check that, too.

Category: postgresql Time: 2016-07-28 Views: 1

Related post

  • How do I reset nautilus to the default configuration? 2010-12-17

    I've reinstalled Nautilus from the maverick repositories after removing nautilus-elementary and the breadcrumb hacks, but I have no longer access to the "preferences" menu and the default toolbar. How do I reset Nautilus to the default configura

  • How do I completely reset Samba to the shipped defaults? 2011-01-14

    Since no one can help me with sharing files from Samba, how can I reset it to the defaults and delete any residual configurations from previous releases? Then, presumably, the GUI sharing would work after I reinstall samba. I tried using dpkg reconfi

  • How do I reset GNOME to the defaults? 2011-08-08

    I was playing around with some settings, and now I need to reset GNOME to it's defaults - how do I do that? --------------Solutions------------- For Unity-related configuration resetting, take a look here. This command will delete your configuration

  • Install a Puppet module to the global modules directory by default? 2015-07-14

    I'm setting up a Puppet master server for the first time. It's configured to use environments: /etc/puppet/puppet.conf: [main] environmentpath = /var/opt/puppet/environments basemodulepath = /var/opt/puppet/modules Whenever I install a module, puppet

  • Datastax community edition in windows - Error in creating the test cluster 2014-09-24

    I recently installed Datastax community edition for windows. As per the installation manual, after successful installation, the Ops Center is supposed to open and show the status of the Test Cluster. Looks like there was an issue, the Test Cluster di

  • How to alter the global broadcast address (255.255.255.255) behavior on Windows? 2009-10-07

    Desired behavior When an application sends a packet to the global broadcast IP address 255.255.255.255, I would like that the packet be sent to the Ethernet global broadcast address (ff:ff:ff:ff:ff:ff), on all interfaces. On Linux and probably other

  • Networking conflict - What is the most common default computer name for Windows? 2010-03-24

    I recently had to change the name of my computer to log onto a public wi-fi spot, because a computer with my name was already logged on. (I asked a guy there what to do after it said there was already a computer named "(omitted)" logged on.) I'v

  • Getting Outlook 2007 to use the global address book? 2010-08-02

    A computer at work has a local address book, that is, the one that the user has set up himself, and the global address book. That is, the global address book is available, but apparently not used. For instance, if the user creates a new email, typing

  • Setting the ribbon to default Browse instead of Edit 2010-09-10

    How do I set the ribbon to default Browse mode instead of default to Edit mode? It is annoying to see the item details in view mode all the time and then click Browse to remove it... --------------Solutions------------- Infact, I was also actually fa

  • How do I enable or disable the global application menu? 2010-10-30

    I'm fairly excited for Unity, as it looks like a promising new direction for Ubuntu. However, I do have a concern - will it be possible to use Unity without the global menu? I have my window manager set to focus-follows-mouse/sloppy focus, and find t

  • Why is Desktop Unity using the global application menu? 2010-11-02

    It was announced in another question that the desktop version of Unity will keep the global menu by default. Here are the facts: The global menu was introduced into UNE to save vertical screen space because at Netbook resolutions the vertical space i

  • Best way to reset some admin options form to default? 2011-01-22

    I got some option pages for my theme. I did this whit add_menu_page, add_submenu_page. I further got settings with register_setting and there i got add_settings_section and add_settings_field(s). The field callback functions come from a class that au

  • Does the global application menu support Java applications? 2011-01-25

    I am a heavy user of Java applications such as Matlab and Maple. Will the global menu support these very important packages? If not, are there plans to do this? --------------Solutions------------- You can have global menu integration for any Java ba

  • Can an LDAP query on AD provide the netbios domain name for a single account when using the Global Catalog? 2011-02-10

    I am using ADSI Edit to look at LDAP properties of a single user account in AD. I see properties such as userPrincipalName, but I do not see one for the fully qualified domain name (FQDN) or the netbios domain name. We will be setting up the Global C

  • Disable auto-hide for the global application menu in Unity 2011-02-11

    Will there be a way to disable auto-hide behavior of global application menu in Unity. While I would prefer to have this behavior disabled by default (as it is bad UX), it looks like it's here to stay, so I'm wondering if, as a user, I will be able t

  • To Reset or Not Reset -- That's the CSS Question 2011-03-03

    Every web browser uses a base stylesheet. It ensures HTML is rendered reasonably well when you don't provide custom CSS. You know the kind of thing: blue for unvisited links, purple for visited links, bold for strong tags, larger text for h1 titles e

  • How does the global variable $user work? 2011-05-09

    Here is my scenario: I create a new user and dynamically create for him some content on which he is the owner. Each user can log in, see his content and edit his profile. I use a block in which I have a menu links to each content and to his profile.

  • Add a reset button to the options page (using Settings API)? 2011-08-31

    How do I add a reset button using the Settings API? I use add_settings_field To add a field...is there a hook to add a reset button? --------------Solutions------------- I asked this Q some couple of month myself. You don't need a hook or anything li

  • Will the VMs on the ESXi cluster be running after disconnection from the network? 2011-11-30

    What will happen if I disconnect the ESXi cluster configured with HA from the switch(I need to change the power source on the main switch) and there is no management port redundancy? I'm going to disable the host monitoring and VM monitoring within H

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.579 (s). 13 q(s)