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

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 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@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 fautes sur
/________/ / 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

=?iso-8859-1?q?Laurent=20FAILLIE?= <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 fautes sur
/________/ / 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