myVertica  

Vertica Blog

Vertica Quick Tip: When Modifying a SEARCH_PATH, Don’t Forget to Include PUBLIC

This blog post was authored by Jim Knicely.

Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can change a session’s search path at any time by calling SET SEARCH_PATH.

When you modify a search path, Vertica automatically tacks on the V_CATALOG, V_MONITOR and V_INTERNAL system schemas, but not the PUBLIC schema. So if still want the user to be able to find objects in the PUBLIC schema, be sure to include it!

Example: dbadmin=> CREATE SCHEMA lost_public_search; CREATE SCHEMA dbadmin=> SHOW search_path; name | setting -------------+--------------------------------------------------- search_path | "$user", public, v_catalog, v_monitor, v_internal (1 row) dbadmin=> SET search_path=lost_public_search; SET dbadmin=> SHOW search_path; name | setting -------------+------------------------------------------------------ search_path | lost_public_search, v_catalog, v_monitor, v_internal (1 row) dbadmin=> SET search_path=lost_public_search, public; SET dbadmin=> SHOW search_path; name | setting -------------+-------------------------------------------------------------- search_path | lost_public_search, public, v_catalog, v_monitor, v_internal (1 row) Have Fun!