Hi all,

Still trying to upgrade from 7.3.4 to 7.4.1 with pg_dumpall.
2 problemes:

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.

And about my previous mails, if I pg_dump the database I have problems
with, it reloads without problems but it I pg_dumpall then I have the
problems I wrote before.

Here part of the log file:

ERROR:  extra data after last expected column
ERROR:  missing data for column "msg_date2"
ERROR:  missing data for column "guilde_valide"
ERROR:  insert or update on table "guilde_rang" violates foreign key constraint "$1"
ERROR:  insert or update on table "guilde_perso" violates foreign key constraint "$1"

here are the table schema:

--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'sdewitte';

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 31845735)
-- Name: messages; Type: TABLE; Schema: public; Owner: sdewitte
--

CREATE TABLE messages (
    msg_cod integer DEFAULT nextval('seq_msg_cod'::text) NOT NULL,
    msg_date date NOT NULL,
    msg_titre character varying(50),
    msg_corps text,
    msg_date2 timestamp with time zone
);


--
-- TOC entry 4 (OID 37161058)
-- Name: messages_msg_cod_key; Type: INDEX; Schema: public; Owner: sdewitte
--

CREATE INDEX messages_msg_cod_key ON messages USING btree (msg_cod);


--
-- TOC entry 5 (OID 37161059)
-- Name: messages_msg_date_key; Type: INDEX; Schema: public; Owner: sdewitte
--

CREATE INDEX messages_msg_date_key ON messages USING btree (msg_date);


--
-- TOC entry 7 (OID 37161115)
-- Name: msg_date2_messages_key; Type: INDEX; Schema: public; Owner: sdewitte
--

CREATE INDEX msg_date2_messages_key ON messages USING btree (msg_date2);


--
-- TOC entry 6 (OID 37161292)
-- Name: messages_pkey; Type: CONSTRAINT; Schema: public; Owner: sdewitte
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (msg_cod);


--
-- TOC entry 8 (OID 37161665)
-- Name: RI_ConstraintTrigger_37161665; Type: TRIGGER; Schema: public; Owner: sdewitte
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER DELETE ON messages
    FROM messages_dest
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'messages_dest', 'messages', 'UNSPECIFIED', 'dmsg_msg_cod', 'msg_cod');


--
-- TOC entry 9 (OID 37161666)
-- Name: RI_ConstraintTrigger_37161666; Type: TRIGGER; Schema: public; Owner: sdewitte
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER UPDATE ON messages
    FROM messages_dest
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'messages_dest', 'messages', 'UNSPECIFIED', 'dmsg_msg_cod', 'msg_cod');


--
-- TOC entry 10 (OID 37161671)
-- Name: RI_ConstraintTrigger_37161671; Type: TRIGGER; Schema: public; Owner: sdewitte
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER DELETE ON messages
    FROM messages_exp
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'messages_exp', 'messages', 'UNSPECIFIED', 'emsg_msg_cod', 'msg_cod');


--
-- TOC entry 11 (OID 37161672)
-- Name: RI_ConstraintTrigger_37161672; Type: TRIGGER; Schema: public; Owner: sdewitte
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER UPDATE ON messages
    FROM messages_exp
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'messages_exp', 'messages', 'UNSPECIFIED', 'emsg_msg_cod', 'msg_cod');


--
-- TOC entry 3 (OID 31845735)
-- Name: TABLE messages; Type: COMMENT; Schema: public; Owner: sdewitte
--

COMMENT ON TABLE messages IS 'Messages internes';


--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'sdewitte';

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 31845953)
-- Name: guilde; Type: TABLE; Schema: public; Owner: sdewitte
--

CREATE TABLE guilde (
    guilde_cod integer DEFAULT nextval('seq_guilde_cod'::text) NOT NULL,
    guilde_nom character varying(100) NOT NULL,
    guilde_description text NOT NULL,
    guilde_valide character varying(2) DEFAULT 'O'::character varying NOT NULL
);


--
-- TOC entry 4 (OID 37161158)
-- Name: guilde_guilde_cod_key; Type: INDEX; Schema: public; Owner: sdewitte
--

CREATE INDEX guilde_guilde_cod_key ON guilde USING btree (guilde_cod);


--
-- TOC entry 6 (OID 37161265)
-- Name: guilde_valide_guilde_key; Type: INDEX; Schema: public; Owner: sdewitte
--

CREATE INDEX guilde_valide_guilde_key ON guilde USING btree (guilde_valide);


--
-- TOC entry 5 (OID 37161364)
-- Name: guilde_pkey; Type: CONSTRAINT; Schema: public; Owner: sdewitte
--

ALTER TABLE ONLY guilde
    ADD CONSTRAINT guilde_pkey PRIMARY KEY (guilde_cod);


--
-- TOC entry 3 (OID 31845953)
-- Name: TABLE guilde; Type: COMMENT; Schema: public; Owner: sdewitte
--

COMMENT ON TABLE guilde IS 'Liste des guildes';


and the offending lines:
guilde:
18	Septiï¿½me Compagnie	En Mï¿½moire de ceux qui, durant des heures sombres, ont inventï¿½ la fameuse et infaillible tactique de la "tenaille"...<br />\r\nLeur esprit n'est pas mort...<br />\r\nA notre tour d'utiliser cette ingï¿½nieuse stratï¿½gie...<br />\r\n<hr WIDTH="100%"><b>LES FONDATEURS:</b><TABLE cellspacing=4 cellpadding=0 border=0><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/alien2.jpg" border="2"> </TD><TD align="left"><font size=-1><b><u>LE BON:</u></b> ALIEN2, le tourbillon mental. Il voit au-delï¿½ de ce qui peut ï¿½tre vu... </font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/gerard.jpg" border="2"> </TD><TD align="left"><font size=-1><b><u>LA BRUTE:</u></b> Le colonel GERARD. "Le fil vert, sur le bouton vert, le fil rouge, sur le bouton rouge..."</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/laerte.jpg" border="2"> </TD><TD align="left"><font size=-1><b><u>LE TRUAND:</u></b> LAERTE, 
 L'homme aux mille noms. Rapide comme l'ï¿½clair, puissant comme le tonnerre, fulgurant comme la foudre.</font></TD></TR></TABLE><hr WIDTH="100%"><TABLE cellspacing=4 cellpadding=0 border=0><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/vodka.gif" border="2"> </TD><TD align="left"><font size=-1>VODKA, l'elfe alcoolique.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/borg.jpg" border="2"> </TD><TD align="left"><font size=-1>L'inflexible BJORN BORG.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/alien1.jpg" border="2"> </TD><TD align="left"><font size=-1>ALIEN1, le nain tempï¿½tueux.</font></TD></TR><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/alien4.jpg" border="2"> </TD><TD align="left"><font size=-1>ALIEN4, l'agile guï¿½rrier.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/misha.gif" border="2"> </TD><TD align="left"><font size=-1
 >MISHA.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/mayol.gif" border="2"> </TD><TD align="left"><font size=-1>MAYOL.</font></TD></TR><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/grut.jpg" border="2"> </TD><TD align="left"><font size=-1>GRUT, l'ï¿½picier perfide.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/Gil_Galad.jpg" border="2"></TD><TD align="left"><font size=-1>GIL-GALAD.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/orguth.jpg" border="2"> </TD><TD align="left"><font size=-1>le lieutenant ORGUTH.</font></TD></TR><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/tassin.jpg" border="2"> </TD><TD align="left"><font size=-1>TASSIN, la terreur des abattoirs.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/pithivier.jpg" border="2"></TD><TD align="left"><font size=-1>PITHIVIERS, le redoutab
 le employï¿½ communal.</font></TD><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/chaudart.jpg" border="2"></TD><TD align="left"><font size=-1>CHAUDARD, le sergent-chef quincallier.</font></TD></TR><TR><TD align="center"><IMG src="http://fandersson.free.fr/delain/images/duron.jpg" border="2"></TD><TD align="left"><font size=-1>Du DUR, du BON, c'est DURON...</font></TD></TR></TABLE><hr WIDTH="100%"><br />\r\n<font size=-1><br />\r\n<br />\r\nNotre mot d'ordre : DEFENDRE l'HONNEUR DE L'ARMEE...<br />\r\n<br />\r\nLA SEPTIEME COMPAGNIE FAIT PARTIE DE L'ALLIANCES DES SERIES B (Septiï¿½me compagnie et gendrames de Saint-Tropez).<br />\r\n<br />\r\nNotre code de conduite:<br />\r\nARTICLE 1 : Ne jamais attaquer un Humain sauf attitude belliqueuse, rï¿½cente, hostile et agressive de sa part (mï¿½me si c'est vrai que l'Homme est mauvais...).<br />\r\nARTICLE 2 : Ne jamais attaquer un Elf sauf attitude belliqueuse, rï¿½cente, hostile et agressive de sa part (mï¿½me si c'est 
 vrai qu'un elf c'est fourbe...).<br />\r\nARTICLE 3 : Ne jamais attaquer un Nain sauf attitude belliqueuse, rï¿½cente, hostile et agressive de sa part (mï¿½me si faut reconnaitre que petit comme c'est c'est tentant...).<br />\r\nARTICLE 4 : Eviter de provoquer un conflit entre notre guilde et d'autre guildes.<br />\r\nLes articles 1 ï¿½ 3 priment sur l'article 4.<br />\r\nARTICLE 5 : Obligation de porter assistance ï¿½ un membre de notre guilde qui le rï¿½clame.<br />\r\nARTICLE 6 : Si jamais on a trï¿½s envie, mais alors trï¿½s trï¿½s envie de bafouer les artciles 1,2,3 et 4 on peut!<br />\r\n<br />\r\nEN CAS DE TRAHISON AVEREE tout membre de la guilde devra avoir pour but la rï¿½demption de l'ï¿½me du traitre qui devra donc vivre 7 nouvelles vies (et donc 7 morts) afin de connaitre le pardon...<br />\r\n<br />\r\nNos conditions d'admission:<br />\r\nN'ayant pas pour but d'ï¿½tre la guilde regroupant le plus de membres (pour une simple question de coordination des actions), il y des conditions ï¿½
  remplir pour postuler. Voici nos critï¿½res (aucun n'est ï¿½liminatoire, mais ne postuler pas si vous pensez remplir seulement quelques critï¿½res...)<br />\r\n-Un bon Karma (la notre est bonne pour l'instant, nous tenons ï¿½ continuer...).<br />\r\n-Pas de PK.<br />\r\n-Une bonne renommï¿½e/Nombre de monstres tuï¿½s (histoire d'ï¿½valuer votre niveau...).<br />\r\n-Avoir tuï¿½ peu de joueurs (nous ne cherchons pas le conflit avec les autres habitants de Delain).<br />\r\nBien-sur comme toute structure notre guilde subi parfois quelques retards administratifs, n'oubliez donc pas que quelques points d'expï¿½rience et/ou quelques brouzoufs distribuï¿½s ï¿½a et lï¿½ aux membres de la guilde peuvent permettre de faire avancer le dossier plus rapidement... ;-)<br />\r\n<br />\r\n<br />\r\nAlliances :<br />\r\n<br />\r\nAlliance des series B : avec "les gendarmes de St-tropez"<br />\r\n<br />\r\n<br />\r\n</font>	O

messages:
71621	28-11-2003	Re HJ coupde gueule	Titre : Re : Re : HJ : Coup de gueule<br />\r\nExpï¿½diteur : Bricogne<br />\r\nDestinataires : Altheos, Merrick, Blade,<br />\r\n<br />\r\n&gt;Tu le fais expres Bricogne ou quoi ???<br />\r\n??? Pourquoi ???<br />\r\n<br />\r\n&gt;personne ne connait l'etendue de tes<br />\r\n&gt;connaissance, et quelqu'un connait-il<br />\r\n&gt;ton equipement ???<br />\r\nNon , bien que je ne le cache pas, mais si j'avais reussi a choper une arme ou un objet extraordinaire telle la Faucheuse, la guilde aurait ete au courant.<br />\r\nEn ce moment je crie a hue et a dia pour recuperer mon arme D8+1 (la numero 28) que j'ai perdu lors de ma mort d'il y a 2 semaines. Je crie partout que j'ai perdu mon &quot;arme fetiche&quot;. Ceux qui savent que normallement tu as la Faucheuse, ils doivent bien rigoler quand ils voient que je me bats pour une simple D8+1.<br />\r\n<br />\r\n&gt;veux tu dire que tu es le premier a<br />\r\n&gt;partager tes infos ??? Tiens une
 <br />\r\n&gt;carte ???<br />\r\nBricogne n'a aucune info speciale. En particulier, il est une brele en cartographie donc il ne peut rien partager.<br />\r\nMarche-Neige (vue au maximum) ou Autrui (n'a que ca a foutre) par contre ont d'excellentes cartes. Le pire c'est que je n'utilise meme pas les cartes de MarcheNeige pour aider Bricogne. J'utilise l'automap de chacun et il m'est arrive une fois de devoir aller a un endroit que Bricogne ne connaissait pas. J'ai tire au pif pour savoir si je passais par le nord ou par le sud, alors que MarcheNeige connaissait le chemin. Coup de bol les des ont donne le bon chemin et Bricogne est arrive rapidement.<br />\r\n<br />\r\n&gt;cela a traine sur le forum, suite a<br />\r\n&gt;la mort de sirius ...<br />\r\nOk, d'accord j'accepte la critique, je me rappelle avoir lu un truc sur une arme mais je n'avais pas realise. Pour moi quand tu parlais de l'arme je croyais que c'etait un expression litteraire pour parler de ton arme fetiche et 
 non pas que c'etait ELLE. M'enfin tu aurais pu dire a tes amis de la guilde avant de le dire sur forum que tu L'avais. En ce moment en cherchant ma d8+1 je dis partout mon arme magique, mon tresor ou mon arme fetiche. Les perso vont donc croire que j'ai une arme superbe ce qui n'est rien car ce n'est qu'une d8+1, or au yeux de Bricogne, il n'a jamais vu mieux.<br />\r\n<br />\r\n&gt;c'est normal non ? D'ailleurs, c'est<br />\r\n&gt;pour cela qu'ils m'ont tue,<br />\r\n&gt;et qu'ils ont ete bien decus :)<br />\r\nOui d'accord mais ce qui m'a mis en colere c'est que franchement tout le monde savait que tu avais la Faucheuse sauf Bricogne.<br />\r\n<br />\r\n&gt;L'artefact est tombe, et a tout de<br />\r\n&gt;suite ete recupere...<br />\r\n&gt;Mais sache que celui qui l'a fait<br />\r\n&gt;n'est pas de notre guilde,<br />\r\n&gt;ne te connait pas, et ne connait<br />\r\n&gt;que peu de monde ...<br />\r\n&gt;Donc, pourquoi t'aurait-il fait<br />\r\n&gt;confiance ? C'est totaleme
 nt RP...<br />\r\nDonc tu as mal joue.<br />\r\nJe suppose que l'artefact a ete recupere par un autre de tes perso ou bien par un ami a toi en vrai.<br />\r\nCet artefact devait rester dans la guilde. Donc pourquoi sur le plan RP comme tu dis, il a ete pris par un autre que Blade et Blade ne s'inquiete pas. Blade aurait du dire a Bricogne de foncer prendre l'artecfact avant que quiconque, meme un de tes personnage le prenne, quitte a se qu'on se tape dessus pour que Bricogne fasse tout pour le recuperer.<br />\r\n<br />\r\nDonc on doit partir a la chase a l'artecfact et tu dois dire qui etait present en vue de Blade quand il est mort pour que l'on aille les voir et voire les tuer tous pour recuperer l'artefact. Meme si c'est un perso a toi.<br />\r\n<br />\r\nQuand j'ai joue les mercenaires qui devaient aller tuer Blade, tu crois que sur le plan humain cela m'amusait de tuer le perso d'un pote. Et comme j'avais dis, Bricogne aurait frappe les Mercenaires pour porter secours 
 a Blade.	Fri 28 Nov 13:09:12.541847 2003 MET


Thanks
-- 
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)

Thu, 15 Jan 2004 15:49:20 +0100 (MET)
ohp@pyrenet.fr
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

Thu, 15 Jan 2004 19:16:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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)

Fri, 16 Jan 2004 12:47:59 +0100 (MET)
ohp@pyrenet.fr
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@pyrenet.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@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)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

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


16 Jan 2004 08:21:52 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: set search_path and pg_dumpall
Robert Treat <xzilla@users.sourceforge.net> writes:
> 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? 

Since no one else commented, I've followed your suggestion.  As of CVS
tip, you get a NOTICE not ERROR in this case:

regression=# set search_path = public,z;
ERROR:  schema "z" does not exist
regression=# create database foo;
CREATE DATABASE
regression=# alter database foo set search_path = public,z;
NOTICE:  schema "z" does not exist
ALTER DATABASE
regression=# select datconfig from pg_database where datname = 'foo';
         datconfig
---------------------------
 {"search_path=public, z"}
(1 row)

If a bogus entry is present in the established search_path value, it's
just ignored (this was true already to handle "$user"):

regression=# \c foo
You are now connected to database "foo".
foo=# show search_path;
 search_path
-------------
 public, z
(1 row)

foo=# select current_schemas(true);
   current_schemas
---------------------
 {pg_catalog,public}
(1 row)

			regards, tom lane

Mon, 19 Jan 2004 14:11:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: set search_path and pg_dumpall 