Re: Error during restore

Carlos Henrique Reimer <carlosreimer(at)yahoo(dot)com(dot)br> writes:
> Im 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