pg_dump/pg_restore and the magic of the search_path

[Arthur Bazin]

Hi everyone !

I have a good question on pg_dump/pg_restore and the search_path.

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.

Ok I understand that there is some modifications on how the dump is
generated.

Now, if you try to restore the dump :
 - PG11 to PG11 no problem
 - PG11 (exported with dump from PG11) to PG13 : no problem
 - PG11 (exported with dump from PG13) to PG13 : no problem
 - PG13 to PG13 : no problem

=> But PG13 to PG11 : problem : the function is not find because it is not
prefixed. Seems legit.

What I don't understand is why PG13 to PG13 works ? If I look in this dump,
we can see the search path is set to '' (empty) and the function isn't
prefixed.
So how can it find where the function is ?
Does PG13 consider that when there is no prefix, we need to use "public" ?

Thank you for your lights on this.
Arthur Bazin

Re: pg_dump/pg_restore and the magic of the search_path

[Arthur Bazin]

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  a écrit :

> > On 31/08/2023 17:08 CEST Arthur Bazin  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
>