Re: Error during restore - Mailing list pgsql-general

From Tom Lane
Subject Re: Error during restore
Date
Msg-id 19827.1108751314@sss.pgh.pa.us
Whole thread Raw
In response to Error during restore  (Carlos Henrique Reimer <carlosreimer@yahoo.com.br>)
List pgsql-general
Carlos Henrique Reimer <carlosreimer@yahoo.com.br> writes:
> I�m trying to restore a client cluster in my Linux box but during the restore the following error is reported:

> ALTER USER "BENK" SET search_path TO '"BENK", public, pg_catalog';
> psql:dumpall7.4.6_0902:14: ERROR:  schema ""BENK", public, pg_catalog" does not exist

I believe we decided this was a backend bug: the ALTER command shouldn't
be trying to verify the validity of the search_path setting. [ digs in
CVS logs... ]  Ah, here we go:

2004-01-19 14:04  tgl

    * src/: backend/access/transam/xlog.c, backend/catalog/namespace.c,
    backend/commands/variable.c, backend/utils/adt/datetime.c,
    backend/utils/adt/pg_locale.c, backend/utils/adt/regexp.c,
    backend/utils/misc/README, backend/utils/misc/guc.c,
    include/access/xlog.h, include/catalog/namespace.h,
    include/commands/variable.h, include/utils/builtins.h,
    include/utils/datetime.h, include/utils/guc.h,
    include/utils/guc_tables.h, include/utils/pg_locale.h,
    interfaces/ecpg/pgtypeslib/dt.h: Repair problem identified by
    Olivier Prenant: ALTER DATABASE SET search_path should not be too
    eager to reject paths involving unknown schemas, since it can't
    really tell whether the schemas exist in the target database.
    (Also, when reading pg_dumpall output, it could be that the schemas
    don't exist yet, but eventually will.)    ALTER USER SET has a
    similar issue.    So, reduce the normal ERROR to a NOTICE when
    checking search_path values for these commands.  Supporting this
    requires changing the API for GUC assign_hook functions, which
    causes the patch to touch a lot of places, but the changes are
    conceptually trivial.

The fix was too complex to consider back-patching to older versions, so
this is only fixed in 8.0.  What you'll have to do in 7.4 is manually
re-issue the ALTER USER command after the dump is reloaded.

It looks like you may also be dealing with an old pg_dumpall bug: it
shouldn't be trying to put quotes around the entire search_path value.
However, there's not much point in worrying about that when the backend
won't take the command anyway until the schema exists ...

            regards, tom lane

pgsql-general by date:

Previous
From: Robert Treat
Date:
Subject: Re: Schema comparison tool
Next
From: Dieter Schröder
Date:
Subject: Re: PostgreSQL Replication