Reset search_path to the global, cluster default

Question

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.

Answer 1

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:

• How does the search_path influence identifier resolution and the “current schema”

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

Related:

• What is the search_path for a given database and user?

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 challenges:

• 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 password.

• 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). So 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.