Thread: Bug #899: pg_dumpall produce erroneous files if the databbase use procedural language.
Bug #899: pg_dumpall produce erroneous files if the databbase use procedural language.
From
pgsql-bugs@postgresql.org
Date:
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
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
--- Bruce Momjian <pgman@candle.pha.pa.us> a écrit : > Hello Bruce, > 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. Please have a look in another message I made in this mailling list : I made some patches in pd_dump.c that correct all theses problems ... > As for loading the languages, did you load this dump > as super-user? ... yes but it is not the probleme : as no user is attached w/ procedural languages (it's clearly commented in the source code of pg_dump), it should generate ACL of then in a scope of a normal user, and not as super user. Again, I solve this issues in my patches. Bye Laurent ===== The misspelling master is on the Web. _________ 100 % Dictionnary Free ! / /(/ Dico / / Pleins d'autres fautessur /________/ / http://go.to/destroyedlolo (#######( / http://destroyedlolo.homeunix.org:8080 Quoi, des fautes d'orthographe! Pas possible ;-D. ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com
Laurent FAILLIE <l_faillie@yahoo.com> writes:
> ... yes but it is not the probleme : as no user is
> attached w/ procedural languages (it's clearly
> commented in the source code of pg_dump), it should
> generate ACL of then in a scope of a normal user, and
> not as super user. Again, I solve this issues in my
> patches.
I don't like the parts of this that are concerned with rearranging dump
order; it's just adding complexity that won't do much except confuse
people working on the code. The one and only true solution for this
class of problems is to make pg_dump pay attention to pg_depend
dependencies. Now that the raw data is available, I think we're past
the point where we should keep adding heuristic kluges that try to
substitute for dependency analysis. I fear they're as likely to break
corner cases as fix 'em :-(
As for the ACL changes, I don't see the point of 'em any more than Bruce
does. Explain again please?
regards, tom lane
Hi Tom,
First of all, I appologies for my english : it's not
my native language so, it's not so easy for me :-(
> > ... yes but it is not the probleme : as no user is
> > attached w/ procedural languages (it's clearly
> > commented in the source code of pg_dump), it
> should
> > generate ACL of then in a scope of a normal user,
> and
> > not as super user. Again, I solve this issues in
> my
> > patches.
>
> I don't like the parts of this that are concerned
> with rearranging dump
> order; it's just adding complexity that won't do
> much except confuse
> people working on the code.
I try to make it as simple as possible, and my goal
was to avoid huge changes in the original source code.
And the easiest, and safer, way was to change dump
order.
With my modifications, pg_dump create data in the same
order as if you have to create a new database by hand,
nothing more :
1/ database creation
2/ schema creation
3/ procedural languages declaration
4/ PL ACL
Step 1 & 2 was already done in the original code. My
changes only add point 3 and 4
/* Now sort the output nicely */ SortTocByOID(g_fout);
+ MoveToStart(g_fout, "ACL LANGUAGE");
+ MoveToStart(g_fout, "PROCEDURAL LANGUAGE");
+ MoveToStart(g_fout, "FUNC PROCEDURAL LANGUAGE"); MoveToStart(g_fout, "SCHEMA"); MoveToStart(g_fout,
"DATABASE"); MoveToEnd(g_fout, "TABLE DATA");
> The one and only true
> solution for this
> class of problems is to make pg_dump pay attention
> to pg_depend
> dependencies.
Sure, but I was facing a trouble that made my backup
unusable, because psql won't accept this file as it
is. It's not acceptable for any live application
where, if something going wrong, you must be fast as
possible : it's impossible if you have many manuals
operations to do.So perhasp my solution is not the better one, but it
solve this issue w/o risks (because, as I said,
pg_dump follows the same procedure as if you do it by
hand) and it's the only one for the moment ;-D
> Now that the raw data is available, I
> think we're past
> the point where we should keep adding heuristic
> kluges that try to
> substitute for dependency analysis. I fear they're
> as likely to break
> corner cases as fix 'em :-(
2 solutions :
1/ ASCII dump file is now obsolete, and remove this
capability for pg_dump,
2/ ASCII dump file is still usefull and make it solide
as a rock.
If you choose the 1st solution, please remember that
is very important for companies to have something
"open", specialy w/ free software. My manager asks me
to choose a database that doesn't use private backup
format : WE HAVE TO TRUST in backup file ; even if we
have to change our database engine, data must be
EASILY importable in another database engine. Some
works has to be done, but it's quite easy w/ ASCII
files comparst to a binary format.
> As for the ACL changes, I don't see the point of 'em
> any more than Bruce
> does. Explain again please?
pg_dump.c file, line 4860 : 'usename' is the owner,
NULL if there is no owner (for languages).
-> No SET SESSION AUTHORIZATION before doing this
task, so they are done by the current active user in
the dump file. In the orginal pg_dump, it could be
done ANYWHERE in the file, and, in my case "anywhere"
was juste after some operations made by a normal user.
So as only the super user can grant language, it fails
:-(
So, I put languages ACL juste after language creation
to avoid such problem.
Have a nice W.E.
Laurent
=====
The misspelling master is on the Web. _________ 100 % Dictionnary Free ! / /(/ Dico / / Pleins d'autres
fautessur
/________/ / http://go.to/destroyedlolo
(#######( / http://destroyedlolo.homeunix.org:8080
Quoi, des fautes d'orthographe! Pas possible ;-D.
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com