From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_dump and search_path |
Date: | 2019-07-09 17:45:12 |
Message-ID: | 126613fd-e0a9-c79f-a400-028107214439@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2019-07-09 17:47:25 | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |
Previous Message | John Lumby | 2019-07-09 17:31:00 | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |