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

Posted May 15, 2018 by Soniya Shah, Information Developer

white cloud in vault type room representing cloud computing
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!