Re: pg_restore & search_path, COPY failed for table "mytable": ERROR: function myinnerfunction(integer) does not exist

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: pg_restore & search_path, COPY failed for table "mytable": ERROR: function myinnerfunction(integer) does not exist
Дата
Msg-id CAKFQuwY+KPBCrqtWeJaAPbrzj7GdogUhTerCW1MfhRqa-oXJ6A@mail.gmail.com
обсуждение исходный текст
Ответ на pg_restore & search_path, COPY failed for table "mytable": ERROR: function myinnerfunction(integer) does not exist  (Jean-Pierre Pelletier <jppelletier@e-djuster.com>)
Список pgsql-hackers
On Thu, Jul 21, 2016 at 1:57 PM, Jean-Pierre Pelletier <jppelletier@e-djuster.com> wrote:

I'm puzzled as to how search_path should be used,.
Should all references be schema qualified inside functions body ?

​Pretty much...you can also do:

CREATE FUNCTION funcname()
SET search_path TO 'other_schemas_needed_by_this_function'
AS $$
[...]
$$​

​You don't have to specify the schema the function is going to reside in...but there is exposure if you don't.​

Or is search_path safe except in the body of functions used in index or
constraints ?

​pg_dump/pg_restore tends to be very conservative in setting search_path.  I'd say you are safe if you can successfully dump/restore and unsafe if you cannot.

​Cross-schema dependencies can be problematic and if you are not willing to test that your omissions are immaterial I'd say you should take the paranoid route an schema-prefix everything - either explicitly or by taking advantage of attribute setting options for functions.

Views, materialized and otherwise, are other areas commonly affected by lax schema specifications.

David J.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: fixes for the Danish locale
Следующее
От: Tom Lane
Дата:
Сообщение: Re: fixes for the Danish locale