2

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?

1
  • If you're wondering what in the heck I'm doing, I'm trying to work around this issue in Flyway.
    – jpmc26
    Jul 29, 2016 at 0:57

1 Answer 1

0

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 […]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.