In PostgreSQL-compatible databases like YugabyteDB, object resolution—whether for functions, tables, or types—relies on the search_path setting. If an object (like a function) seems to be missing despite being created, it’s often because its schema isn’t part of the current search_path.
This isn’t unique to functions like uuid_generate_v4() from the uuid-ossp extension; it can happen with any object whose schema isn’t explicitly referenced or included in the search_path.
Example:
After installing the uuid-ossp (or a similar) extension in a YugabyteDB database, you might encounter the following error when trying to call the uuid_generate_v4() function:
ERROR: function uuid_generate_v4() does not exist
By default, installing the uuid-ossp extension creates the uuid_generate_v4() function in the public schema.
If a user’s search_path is explicitly set to an empty value (''), YugabyteDB won’t search the public schema, leading to a “function does not exist” error.
Consider an example involving a new user, where the search_path is set by default to "$user", public.
yugabyte=# CREATE USER test;
CREATE ROLE
yugabyte=# SELECT usename, useconfig FROM pg_user WHERE usename = 'test';
usename | useconfig
---------+-----------
test |
(1 row)
yugabyte=# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SHOW search_path;"
search_path
-----------------
"$user", public
(1 row)
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SELECT uuid_generate_v4();"
uuid_generate_v4
--------------------------------------
d8610019-38c2-4411-adb3-2728a0268156
(1 row)
yugabyte=#
That worked as expected.
But what if the search_path is cleared?
yugabyte=# ALTER USER test SET search_path TO '';
ALTER ROLE
yugabyte=# SELECT usename, useconfig FROM pg_user WHERE usename = 'test';
usename | useconfig
---------+----------------------
test | {"search_path=\"\""}
(1 row)
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SHOW search_path;"
search_path
-------------
""
(1 row)
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SELECT uuid_generate_v4();"
ERROR: function uuid_generate_v4() does not exist
LINE 1: SELECT uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Restoring expected behavior requires resetting the user’s search_path to the default value.
yugabyte=# ALTER USER test SET search_path TO DEFAULT;
ALTER ROLE
yugabyte=# SELECT usename, useconfig FROM pg_user WHERE usename = 'test';
usename | useconfig
---------+-----------
test |
(1 row)
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SHOW search_path;"
search_path
-----------------
"$user", public
(1 row)
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SELECT uuid_generate_v4();"
uuid_generate_v4
--------------------------------------
fb96dc7e-e148-4790-8dc8-f19dba0dee26
(1 row)
Avoid setting search_path to an empty string; if you override it, be sure to explicitly include public.
yugabyte=# CREATE SCHEMA test_schema AUTHORIZATION test;
CREATE SCHEMA
yugabyte=# ALTER USER test SET search_path TO test_schema, public;
ALTER ROLE
Alternatively, use the schema-qualified name:
yugabyte=# ALTER USER test SET search_path TO '';
ALTER ROLE
yugabyte=# \! ysqlsh -h $(hostname -I) -U test -c "SELECT public.uuid_generate_v
4();"
uuid_generate_v4
--------------------------------------
017a971a-2737-4664-8fb7-8b36baa744e9
(1 row)
Have Fun!
