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 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
dataand on stored procedure.
Best regards,
Laurent
Sample Code
No file was uploaded with this report
