Update the current session's default parameter value to reflect a new default

Question

Say I ALTER the database default search_path to include a new schema:

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

Then I also update the current search_path to match:

SET search_path "$user",public,other_schema;

This is great. Now I can use objects from that schema in my current session without including the schema.

...unless someone RESETs the parameter:

RESET search_path;

This doesn't reset it back to the new default. It resets it back to the original default before the session started. Even COMMITing the current transaction doesn't change this.

Trying to UPDATE pg_settings doesn't work:

UPDATE pg_settings SET reset_val = '"$user",public,other_schema' WHERE name = 'search_path';
SELECT * FROM pg_settings WHERE name = 'search_path';

This shows no change to the view and no difference in the behavior of RESET.

I could see use cases for this, but in my use case, I need this to not happen. How can I get PostgreSQL to update the current session default alongside my other changes, so that RESET will set it back to that value? Especially following a COMMIT?

Answer 1

DATABASE-level default settings take effect upon connecting. You need to reconnect for the new database defaults to become your connection defaults (if they are not overridden on the database+role level).

From the documentation:

This will save the setting (but not set it immediately). In subsequent connections to this database it will appear […]