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:

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:

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.