Re: Bug #899: pg_dumpall produce erroneous files if the databbase

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?

---------------------------------------------------------------------------

pgsql-bugs(at)postgresql(dot)org wrote:
> Laurent Faillie (l_faillie(at)yahoo(dot)com) 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073