Bug #899: pg_dumpall produce erroneous files if the databbase use procedural language.

Laurent Faillie (l_faillie@xxxxxxxxx) 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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly