pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)

When trying to move a database from 8.3 to 9.2.5, the plain text pg_dump
did not restore without errors. (I used pg_dump from 9.2.5) The problem
seems to relate to references to other schemas and the schema search paths.

First, here's the error message:

psql:/tmp/spc_test_1005_dump_with_pg_9_2_5.orig.sql:451247: ERROR:
relation "tbl_housing_unit" does not exist
LINE 3: SELECT a.housing_project_code FROM tbl_housing_unit a WHERE...
^
QUERY:

SELECT a.housing_project_code FROM tbl_housing_unit a WHERE
LOWER(a.housing_unit_code)=LOWER($1) LIMIT 1;

CONTEXT: SQL function "housing_project_from_unit" during inlining
COPY tbl_unit_absence, line 1: "1 [data snipped] \..."

This database has "public" and "spc" schemas. tbl_housing_unit (table) and
housing_project_from_unit (function) are in the public schema. The error
occurs during this operation:

COPY tbl_unit_absence (unit_absence_id, client_id, housing_project_code,
housing_unit_code, unit_absence_date, unit_absence_date_end,
absence_reason_code, comment, added_by, added_at, changed_by, changed_at,
is_deleted, deleted_at, deleted_by, deleted_comment, sys_log) FROM stdin;

tbl_unit_absence is in the spc schema. It has a constraint that uses the
housing_project_from_unit (from public). Prior to running the copy
command, the dump file has done a "SET search_path = spc, pg_catalog;" and
so it doesn't find tbl_housing_unit from public.

I was able to get my database restored by changing the SET search_path
commands to "spc, public, pg_catalog" and "public, spc, pg_catalog" so this
isn't a practical issue for me. Even more so because those relations were
all meant to be in the public schema--things just got a bit screwy.

But I haven't seen anything that indicates this should stop a pg_dump from
working, and so wonder if this should be reported as a bug. It might be a
known limitation, or maybe it's just tough luck if you cross schemas?

I'm happy to provide more information if it's helpful. Thanks.

Ken

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.