Re: set search_path and pg_dumpall

ohp@pyrenet.fr writes:
> When a serch_path has been set, pg_dumpall correctly output a alter
> database xxx set search_path to 'xxx' but *BEFORE* the schema is created
> so it doesn't work.

Hm.  It's worse than that really: in ALTER DATABASE SET, we are trying
to check the search path in the wrong context.  Consider:

regression=# create database foo;
CREATE DATABASE
regression=# alter database foo set search_path to 'fooschema';
ERROR:  schema "fooschema" does not exist

If we are not connected to database foo then we have no way to tell
whether the requested search path is valid.  Presently the backend
is checking the path against the schemas in the *current* database,
which is obviously bogus.

A closely related case is this (which also represents a scenario
where pg_dumpall will fail at the moment):

regression=# create user foo;
CREATE USER
regression=# alter user foo set search_path to 'fooschema';
ERROR:  schema "fooschema" does not exist

I am inclined to think that raising an error here isn't a good idea
either, since it's quite possible that the user's search path isn't
meant to be used in the current database.  We don't even have
any way to tell which database it is meant to be used in.

So I'm leaning to the thought that we shouldn't change pg_dumpall's
behavior, but instead should relax the backend's error checking so
that it doesn't reject these cases.  To be specific, I think that
for "ALTER DATABASE/USER SET search_path", we only want to do a
syntactic check that the search path is valid (ie, it's a list of
identifiers), and not insist that it refer to existing schemas.

The only case where checking schema existence is arguably useful is
ALTERing the current database --- but if we do that, then we still
have to do something to change pg_dumpall's behavior, and existing
pg_dumpall scripts are still broken.  So I'm content to say that we
won't check regardless of which database is the target.

Next question is how exactly to make the change.  It seems like a really
clean solution would involve adding another GucSource or GucContext
value to denote that we're trying to validate an ALTER ... SET value,
and changing the API for GUC variable assign hooks so that
assign_search_path could find out that that's what we're doing.  Should
we go to that much trouble, and if so what should the modified API be?
At the moment search_path seems to be the only GUC variable that has a
context-sensitive checking routine, so maybe a quick kluge for just this
variable is sufficient.  I have a feeling the problem may come up in the
future with other variables, though.

Comments?
        regards, tom lane