Function uuid_generate_v4() Not Found After Installing uuid-ossp or similar Extension in YugabyteDB

Environments

This KB applies To

• YugabyteDB
• PostgreSQL
• Applications using uuid-ossp or similar extensions

In PostgreSQL-compatible databases like YugabyteDB, object resolution (for functions, tables, types, etc.) depends on the search_path setting. If a function or any other object appears to be missing—even though it exists—the issue is often due to how the search_path is configured.

This behavior is not specific to functions like uuid_generate_v4() from the uuid-ossp extension. It can occur with any object if its schema is not included in the effective search_path.

In this Knowledge Base article, we use uuid_generate_v4() as an example to demonstrate the issue, its root cause, and how to resolve it using a reproducible test case.

Summary

After installing the uuid-ossp or similar extension in a YugabyteDB database, users may encounter the following error when invoking the uuid_generate_v4() function:

ERROR: function uuid_generate_v4() does not exist

This article explains the root cause, provides a step-by-step reproduction, and shares the resolution and best practices.

Cause

By default, the uuid_generate_v4() function is created in the public schema when uuid-ossp is installed.

If a database user has an explicitly empty search_path set (i.e., ''), YugabyteDB will not search the public schema for objects, resulting in a “function does not exist” error.

Reproduction Steps

1. Create a new user:

   CREATE USER test;
   

2. Verify no custom search_path is set:

   SELECT usename, useconfig FROM pg_user WHERE usename = 'test';
   

   Expected:

    usename | useconfig
   ---------+-----------
    test    | 
   

3. Install the extension (as a superuser or privileged user):

   CREATE EXTENSION "uuid-ossp";
   

4. Test the function:

   ysqlsh -U test -c "SELECT uuid_generate_v4();"
   

   Output:

    uuid_generate_v4
   --------------------------------------
    a1b2c3d4-... (random UUID)
   

Simulating the issue

5. Set an empty search_path for the user:

   ALTER USER test SET search_path TO '';
   

6. Verify the change:

   SELECT usename, useconfig FROM pg_user WHERE usename = 'test';
   

   Output:

    usename |      useconfig
   ---------+----------------------
    test    | {"search_path=\"\""}
   

7. Try to call the function again:

   ysqlsh -U test -c "SELECT uuid_generate_v4();"
   

   Output:

   ERROR:  function uuid_generate_v4() does not exist

Resolution

To restore expected behavior, reset the user's search_path to the default:

ALTER USER test SET search_path TO DEFAULT;

Verify:

SELECT usename, useconfig FROM pg_user WHERE usename = 'test';

Expected:

 usename | useconfig
---------+-----------
 test    | 

Retry the function:

ysqlsh -U test -c "SELECT uuid_generate_v4();"

Output:

 uuid_generate_v4
--------------------------------------
 d5cd4e0b-... (random UUID)

Recommendations 

• Avoid setting search_path to an empty string.
• If you must override it, explicitly include public:

  ALTER USER test SET search_path TO myschema, public;
  

Alternatively, use the schema-qualified name:

SELECT public.uuid_generate_v4();

Additional Information

Why This Happens

When an object is created in a schema (typically public) but the search_path is empty or doesn't include that schema, the database can't resolve unqualified references. You must either:

• Include the correct schema in the search_path, or
• Use the fully qualified name (e.g., public.uuid_generate_v4())

Checking for Misconfigurations

You can inspect user and database-level search_path overrides using:

SELECT usename, useconfig FROM pg_user WHERE usename = '<user>';

SELECT r.rolname, d.datname, rs.setconfig
FROM pg_db_role_setting rs
LEFT JOIN pg_roles r ON r.oid = rs.setrole
LEFT JOIN pg_database d ON d.oid = rs.setdatabase
WHERE r.rolname = '<user>' OR d.datname = '<db>';

Look for suspicious values like {"search_path="""}.