pg_dump/pg_restore and the magic of the search_path

[Arthur Bazin <arthurbazin@xxxxxxxxx>]

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