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.
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:
You can have search_path set at the following levels and this is the order PostgreSQL decides which search path setting to use:
ALTER FUNCTION some_func() SET search_path=public,utility;
ALTER ROLE postgres IN DATABASE mydb SET search_path = public, utility;
ALTER ROLE postgres SET search_path = public,utility;
ALTER DATABASE mydb SET search_path = public, utility;
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.
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
schema3,pg_catalog and your index that utilizes function in
schema1.func1 fails to be created because,
can't find it's companion
You schema qualify public.func2 in the definition of your schema1.func1OR You set the search_path of schema1.func1 to include public.