Different search path behavior for functions in YugabyteDB compared to PostgreSQL

Question

[Question posted by a user on YugabyteDB Community Slack]

I'm investigating switching from PostgreSQL to YSQL and I'm not able to set up our Rails application against YSQL running locally.

I'm trying to import a postgresql dump but I'm getting different behavior with search paths. If I run the following on Postgresql (version 14.1 in my case) it works fine:

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
SELECT pg_catalog.set_config('search_path', '', false);
SELECT gen_random_uuid(); #=>  9e7b2d3f-c76f-4f7c-bc31-33451995afdb

With ysqlsh (11.2-YB-2.13.0.0-b0) I get this however:

ERROR: function gen_random_uuid() does not exist

This means I can't import my Rails structure.sql schema because the pg dump it does sets the search path to ''. Then this fails:

CREATE TABLE public.data (id uuid DEFAULT gen_random_uuid() NOT NULL);

Answer 1

It works in PostgreSQL 14 because gen_random_uuid() is part of the core since PG13, and then not needed to see the functions created by the extensions in the public schema. The current YugabyteDB version is compatible with PG11 where I guess the behavior is the same.