Schema and search_path surprises

This was an article we were meaning to write and got reminded by Szymon Guz latest PostgreSQL search_path behavior. The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. The pg_catalog schema is one that never requires schema qualification even if you don't add it to your schema search_path. Schemas are searched in the order they are listed and when creating new objects (e.g. tables, views, function, types) that are not schema qualified, they are always created in the first schema of the search path. Any objects not in a schema listed in the search_path must be schema qualified.

Great feature but confusing

The search_path behavior in PostgreSQL while a great feature, causes many newbies and some experienced folk some major confusion. We exploit / abuse it a lot for building multi-tenant web applications where by we control the tables being hit by the application account we are using and even what tables stored functions are hitting. There's a whole art to exploiting/abusing search_paths which we'll leave as a discussion for another day.

The main confusing thing with search_path is there are so many levels at which you can change search_path and you have to remember the order of precedence. Case in point, we've had a few number of PostGIS newbie users puzzled why their tables require schema qualification and why the search_path settings they have set do not work. This often happens for users using topology or people who decide to put postgis in its own schema. Issues arise for the following reasons:

• They set the search_path at the database or server level but did not start a new connection after setting it. Unless if set at the function or session level, the new search_path only takes effect with new connections.
• the search_path being used is at the user level specified search_path and the database default search_path is something different
• The user only set the search_path for the session and assumed the session set search_path is permanently set
• The user neglected to set the database search_path and assumed all schemas are automatically searched

So many levels search_path can be set

You can have search_path set at the following levels and this is the order PostgreSQL decides which search path setting to use:

• At the function level - only lasts for life of execution of function within function: ALTER FUNCTION some_func() SET search_path=public,utility;
• At the session level - only lasts for the life of the session: set search_path=public,utility;
• At the database user level - only takes affect for new sessions: ALTER ROLE postgres IN DATABASE mydb SET search_path = public, utility;
• At the server user level -- only takes affect for new sessions: ALTER ROLE postgres SET search_path = public,utility;
• At the database level -- only takes affect for new sessions: ALTER DATABASE mydb SET search_path = public, utility;

Views always have schema qualified tables

One other caveat, while for functions, you can get them to utilize different tables with same names in different schemas based on the current search_path, views always have the tables schema qualified. So you can't really abuse search paths in views or at least not directly.

Restore changes search_path during load: big gotcha for some indexes

When you restore data, you may have noticed search_paths are always set. This can on occasion cause certain indexes not to be recreated. For example let's say you have a function schema1.func1, which references another function in public.func2. In normal operation this works just fine except when you try to restore. You have a table in schema3 that uses said function schema1.func1. When your schema3 table is loaded the search_path is temporarily changed to schema3,pg_catalog and your index that utilizes function in schema1.func1 fails to be created because, schema1.func1 can't find it's companion public.func2 unless You schema qualify public.func2 in the definition of your schema1.func1OR You set the search_path of schema1.func1 to include public.