Re: pg_dump and search_path

On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:
> On 7/9/2019 7:02 AM, Adrian Klaver wrote:
>> On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:
>>> I have a custom search_path:
>>>
>>> # show search_path;
>>>             search_path
>>> ----------------------------------
>>>   "staging, transient, pg_catalog"
>>> (1 row)
>>>
>>> I ran `pg_dump --schema-only` and the only reference in the output to 
>>> search_path is:
>>>
>>>    SELECT pg_catalog.set_config('search_path', '', false);
>>>
>>> Then one of my functions which does not reference the full name of a 
>>> table with its schema fails with "relation [rel-name] does not exist".
>>
>> Where is this failing?
>>
>> Do you have the search_path set in the config for the server you are 
>> dumping to?
> 
> It is failing during the Restore operation.  I can provide more 
> information if I'll understand what you mean exactly by "Where".

Yes, because I cannot replicate with just a function:

CREATE OR REPLACE FUNCTION public.search_path_test(integer)
  RETURNS integer
  LANGUAGE plpgsql
AS $function$
BEGIN
     perform * from test_tbl;
     RETURN 1;
END;
$function$

test_(postgres)# \d test_tbl
            Table "test_schema.test_tbl"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |

pg_dump -d test -U postgres -x -p 5412 -Fc -f dump_search_path.out
pg_restore --single-transaction -d test -c -U postgres -p 5412 
dump_search_path.out

SELECT pg_catalog.set_config('search_path', '', false);

postgres-2019-07-09 10:37:32.488 PDT-604LOG:  statement: CREATE FUNCTION 
public.search_path_test(integer) RETURNS integer
             LANGUAGE plpgsql
             AS $$
         BEGIN
             perform * from test_tbl;
             RETURN 1;
         END;
         $$;



postgres-2019-07-09 10:37:32.489 PDT-604LOG:  statement: ALTER FUNCTION 
public.search_path_test(integer) OWNER TO aklaver;


My guess is the function is being used somewhere.

> 
> search_path is not set int he config, but rather with ALTER DATABASE SET 
> search_path TO ... but I have executed that prior to the RESTORE on the 
> target database.  Would it make a difference if I set it in the config?
> 
> Thanks,
> 
> Igal
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com