Thread: 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" error
>
> This 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