Re: set search_path and pg_dumpall

I can't be the only one forsee frustration from users who typo the set
search_path statement and then can't figure out why their tables aren't
showing up... can we emit a warning that not all of the schemas in the
search path were found?

Robert Treat

On Fri, 2004-01-16 at 06:47, ohp(at)pyrenet(dot)fr wrote:
> 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(at)sss(dot)pgh(dot)pa(dot)us>
> > To: ohp(at)pyrenet(dot)fr
> > Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>,
> > Peter Eisentraut <peter_e(at)gmx(dot)net>
> > Subject: Re: [HACKERS] set search_path and pg_dumpall
> >
> > ohp(at)pyrenet(dot)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(at)pyrenet(dot)fr
> ------------------------------------------------------------------------------
> Make your life a dream, make your dream a reality. (St Exupery)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL