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

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