Well, we do dump/load procedural languages before function, as you can see in your output. The plpgsql has TOC #247 while the create language has TOC #252.
As for loading the languages, did you load this dump as super-user? --------------------------------------------------------------------------- [EMAIL PROTECTED] wrote: > Laurent Faillie ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > pg_dumpall produce erroneous files if the databbase use procedural language. > > Long Description > Hi all, > > I'm working to migrate my database from postgresql 7.2.2 to 7.3.2 > It run on an HP-UX 11 box. > > So, I use 7.3.2 pg_dumpall binary to extract my previous database using the > following command > > pg_dumpall -c -h eux170 > > and, then I try to import these data into my new one (on a test server) > > psql -a -v ON_ERROR_STOP template1 < /datas/depot/tmp/db > > which fails for 2 problem : > > First : > ======= > > -- > -- TOC entry 247 (OID 16595) > -- Name: plpgsql; Type: ACL; Schema: ; Owner: > -- > REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC; > ERROR: permission denied > GRANT ALL ON LANGUAGE plpgsql TO PUBLIC; > ERROR: permission denied > > Why ? Because, in this step, the user remain as it was in the previous step of the > file, and in my case, it's a normal user. > > I think the fix will be to switch to the super user before doing this. > > second : > ======== > > -- > -- TOC entry 252 (OID 16610) > -- Name: arrondi_date (timestamp with time zone); Type: FUNCTION; Schema: ; Owner: > faillie > -- > CREATE FUNCTION arrondi_date (timestamp with time zone) RETURNS date > AS ' > BEGIN > IF EXTRACT(HOUR FROM $1) > 20 THEN > RETURN DATE($1) + 1; > ELSE > RETURN DATE($1); > END IF; > > END; > ' > LANGUAGE plpgsql; > ERROR: language "plpgsql" does not exist > > Why ? Because plpgsql is not existing at this step ... it will be added to the > database ... afterwards. > > The solution is obviously to create all languages just after database and user > creation, but BEFORE doing anything on data and on stored procedure. > > Best regards, > > Laurent > > > Sample Code > > > No file was uploaded with this report > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
