pg_dump/restore issue with schemas
Seems to be on 7.3 and 7.4beta (not tested the newest - probably be next week
before I get a chance to test that).
The situation seems to be:
table public.a
function reports.f()
The function refers to "a" without the full schema (i.e. not as "public.a")
The function was originally defined with its name as "reports.f" while
search_path = public...
On dump/restore the search_path is set to reports, pg_catalog so of course you
get a "no relation a" error
The work-around is obviously to manually edit the SET search_path line above
the function to include the public schema. Obviously, you could alter
pg_dump/restore to set search_path = reports,public,pg_catalog but that won't
help you in the (unlikely?) case that the original path was foo,pg_catalog.
Not sure what a general fix for this would look like without keeping track of
what search_path settings were at the time the function was originally
designed.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
table public.a
function reports.f()
The function refers to "a" without the full schema (i.e. not as "public.a")
The function was originally defined with its name as "reports.f" while
search_path = public...
On dump/restore the search_path is set to reports, pg_catalog so of
course you get a "no relation a" error
This is an SQL function right?
This seems to be an additional and fairly critical reason to disable
checking of SQL function bodies during a reload. I had already proposed
doing so:
http://archives.postgresql.org/pgsql-hackers/2003-09/msg00970.php
but that seemed to have been greeted by a collective yawn, so I was not
planning to do it for 7.4. Now I'm thinking we had better do it.
regards, tom lane
On Friday 03 October 2003 16:20, Tom Lane wrote:
Richard Huxton <dev@archonet.com> writes:
On dump/restore the search_path is set to reports, pg_catalog so of
course you get a "no relation a" errorThis is an SQL function right?
It was indeed.
What particularly threw me was the fact that it just complained about the
unqualified table name. I assumed it was a dependency-related issue and then
got confused because the table clearly *did* exist.
This seems to be an additional and fairly critical reason to disable
checking of SQL function bodies during a reload. I had already proposed
doing so:
http://archives.postgresql.org/pgsql-hackers/2003-09/msg00970.php
but that seemed to have been greeted by a collective yawn, so I was not
planning to do it for 7.4. Now I'm thinking we had better do it.
Is that what you do with views? I thought the problem might exist there, but a
quick test seems to show they're fine.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
This seems to be an additional and fairly critical reason to disable
checking of SQL function bodies during a reload.
Is that what you do with views?
No. Reverse-listing of views takes the current schema path into account
when deciding whether to qualify names, so it works correctly with
pg_dump. But there's no reverse-listing of function bodies, they are
just strings ...
regards, tom lane