Re: pg_dump/pg_restore and the magic of the search_path

Hi, thanks for your answer !
Sorry for being late...

I'm sure :-)

But I made more research and find the problem : the function I use...

I'm using the gen_random_uuid() function wich is included into postgresql
core since v13 and was originally into the pgcrypto extension.
So when you don't prefix the function call, PostgreSQL don't use any prefix
because this is a core function and there is no need for prefix...
When you prefix it, PostgreSQL keep the prefix in case you call another
function (that you CREATE into another schema for exemple).

When you use a function that is stored into the public schema but is not
part of the core (personnal functions or functions from extensions)
PostgreSQL keep the prefix you add into the CREATE statement or add a
prefix if you omit it regarding the place where the function is stored
(depending of the search_path you have when you run the statement).

So, there is no problem it was a misunderstanding on my side on how pg_dump
works with core function.
Maybe adding the "pg_catalog" prefix could be an improvment, I will propose
that.

Thanks !

Best regards
Arthur Bazin

Le jeu. 31 août 2023 à 23:47, Erik Wienhold <ewie(at)ewie(dot)name> a écrit :

> > On 31/08/2023 17:08 CEST Arthur Bazin <arthurbazin(at)gmail(dot)com> wrote:
> >
> > Consider that we have a function in the public schema witch is named
> > my_function_in_public.
> >
> > In PG11 this table :
> > CREATE TABLE public.test_dump (
> > id TEXT DEFAULT my_function_in_public()
> > );
> > When you dump this table with the pg11 binaries, you obtain this script :
> > CREATE TABLE public.test_dump (
> > id TEXT DEFAULT public.my_function_in_public()
> > );
> > => the schema prefix have been added to the function by pg_dump.
> >
> > In PG13, the same table :
> > CREATE TABLE public.test_dump (
> > id TEXT DEFAULT my_function_in_public()
> > );
> > When you dump this table with the pg13 binaries, you obtain this script :
> > CREATE TABLE public.test_dump (
> > id TEXT DEFAULT my_function_in_public()
> > );
> > => the schema prefix have not been added.
>
> Are you sure that my_function_in_public was created in schema public on
> pg13?
> I cannot reproduce this on 13.12. However, I can reproduce it when
> creating
> that function in pg_catalog instead of public. The dump does not include
> pg_catalog.my_function_in_public though.
>
> --
> Erik
>