Sample Header Ad - 728x90

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

2 votes
1 answer
301 views
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?
Asked by jpmc26 (1652 rep)
Jul 29, 2016, 12:08 AM
Last activity: May 10, 2025, 07:06 PM