Thread: pg_dump/pg_restore and the magic of the search_path
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()
);
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()
);
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()
);
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()
);
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
> On 31/08/2023 17:08 CEST Arthur Bazin <arthurbazin@gmail.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
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@ewie.name> a écrit :
> On 31/08/2023 17:08 CEST Arthur Bazin <arthurbazin@gmail.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