When Modifying a SEARCH_PATH, Don’t Forget to Include PUBLIC

[Deleted User] · May 2018

Jim Knicely authored this tip.

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!