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
