Re: set search_path and pg_dumpall

Thanks for the input Tom.
INMHO, this kind of statement should'nt cause any error even if the schema
doesn't exit *yet*; because:
1) if the script comes for pg_dump[all], we KNOW that this statement is
right
2) if it's typed in psql, and the user names the wrong schema, he will
find out very quickly (benn there, done that)...

You didn't reply to the second part of my mail witch prevents me to go to
7.4.1

Regards
On Thu, 15 Jan 2004, Tom Lane wrote:

> Date: Thu, 15 Jan 2004 19:16:47 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>,
>      Peter Eisentraut <peter_e@gmx.net>
> Subject: Re: [HACKERS] 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
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)