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, [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] > > Cc: pgsql-hackers list <[EMAIL PROTECTED]>, > > Peter Eisentraut <[EMAIL PROTECTED]> > > Subject: Re: [HACKERS] set search_path and pg_dumpall > > > > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > ------------------------------------------------------------------------------ > 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
