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.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing
list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.
On 10/21/2013 12:50 AM, Ken Tanzer wrote:
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] \..."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.
What was the pg_dump command you used to dump the database?
So to be clear, housing_project_from_unit was not restored at all unless
you manually changed the search_path or did Postgres throw an error at
restore it at a later point?
Ken
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Ken Tanzer <ken.tanzer@gmail.com> writes:
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.
What this looks like to me is that you have a SQL function which isn't
protecting itself against changes in search_path. It could fail in any
context where somebody's changed search_path, not just a restore run.
You should consider fully qualifying the table reference in the function's
source code, or adding a "SET search_path" clause to the function
definition.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Oct 21, 2013 at 6:55 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
What was the pg_dump command you used to dump the database?
pg_dump -p 3333 -h localhost -F p -U spc_ag spc_test_1005 >
~/spc_test_1005_dump_with_pg_9_2_5.sql
So to be clear, housing_project_from_unit was not restored at all unless
you manually changed the search_path or did Postgres throw an error at
restore it at a later point?
I believe housing_project_from_unit (function) was created.
tbl_unit_absence was created, and the error was caused when it tried to
populate tbl_unit_absence, because it had the constraint using
housing_project_from_unit(). That function couldn't find
tbl_housing_project, because it was in the public schema, which was not in
the search path at that point.
I didn't actually try it, but it's a plain SQL dump, and there were no
attempts to populate tbl_unit_absence further on in the file.
Ken
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing
list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.
On Mon, Oct 21, 2013 at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
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 searchpaths.
What this looks like to me is that you have a SQL function which isn't
protecting itself against changes in search_path. It could fail in any
context where somebody's changed search_path, not just a restore run.
You should consider fully qualifying the table reference in the function's
source code, or adding a "SET search_path" clause to the function
definition.
That all sounds about right. It's just that my previous experience had
been "you dump a file with pg_dump, and it restores OK." These relations
were all _supposed_ to be in the same schema, so it may reflect a poor or
wacky (or accidental!) use case, but the database could run OK with the
search path set as needed, whereas the dump seems destined to fail. There
may be no way around it, but it's helpful for me to know that a dump is not
guaranteed to restore!
Cheers,
Ken
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing
list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.