Re: Bug #899: pg_dumpall produce erroneous files if the databbase - Mailing list pgsql-bugs

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@postgresql.org wrote:
> Laurent Faillie (l_faillie@yahoo.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
normaluser. 
>
> 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
dataand 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@postgresql.org so that your
> message can get through to the mailing list cleanly
>

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