Re: pg_dump and search_path

On 7/9/2019 10:45 AM, Adrian Klaver wrote:
> 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.

I see.  Yes, the function is used by an INDEX.  So somewhere down the 
line in the pgdump file I have:

   CREATE INDEX ix_items_tags ON staging.items USING gin 
(staging.some_func_returning_array(col1));

Igal