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.
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.
Ken
--
![]()
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
