Re: pg_dump and search_path

On 7/9/19 4:24 PM, Igal @ Lucee.org wrote:
> 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));

Well you are part of the way there, the function is schema qualified:)

I will leave it others more knowledgeable on the subject as to whether a
function as a GIN expression is a good idea or not.

>
> Igal
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com