Forum OpenACS Q&A: pg_dump 7.1RC2 problems

In preparing to run the 3.2.4 >> 3.2.5 upgrade scripts, I decided to first dump and restore my database first, just to be sure that would work. It doesn't.

The first thing it encounters is an ordering problem, the function 'user_vacations_kludge' is used before it is created (by two views). salary_divisor, salary_multiplier and im_projects_level have the same problems.

Then there are three messages 'ERROR: DefineIndex: opclass "float4_ops" does not accept datatype "float8"' which I think are related to:

CREATE  INDEX "ec_categories_sort_idx" on "ec_categories" using btree
( "sort_key" "float4_ops" );
CREATE  INDEX "ec_subcategories_idx2" on "ec_subcategories" using
btree ( "sort_key" "float4_ops" );
CREATE  INDEX "ec_subsubcategories_idx2" on "ec_subsubcategories"
using btree ( "sort_key" "float4_ops" );

which are the only three float4_ops grep will find.

Then a lot of:

Function 'setval(unknown, int4, unknown)' does not exist
        Unable to identify a function that satisfies the given
argument types
        You may need to add explicit typecasts

Which may come from the double quoting or something as there are a similar amount of these:

--
-- TOC Entry ID 3 (OID 21579)
--
-- Name: lob_sequence Type: SEQUENCE SET Owner: 
--

SELECT setval ('"lob_sequence"', 1, 'f');

Is this something alarming, or is it something as simple as loading postgres.sql as in 7.0?

Nope, I think you understood wrong. I am now running 3.2.4 on Postgres
7.1RC2 and want to run the upgrade script agains that.

The 7.1RC2 pg_dump got it wrong. I have already dumped and restored my
3.2.4 from 7.0 to 7.1 and now want to test the pg_dump/restore cycle
for 7.1.

Have fun with your midterm first tho 😊

Ouch. Partially my mistake. The test I actually ran was: dump from 7.1, restore in 7.0. One of the pitfalls in a dual-pg system is that psql (correctly) doesn't warn you when you connect to 7.0, but pg_dump does. I think I will drop a feature request with the postgres team to dump a little bit more connection info for psql (or submit a patch). It would seem that restoring a dump from 7.1 to 7.0 works rather well 😉.  If you run this sed script agains the dump file: <pre>s/^SELECT setval [(]'"(.*)"/SELECT setval ('
/g</pre>, the setval calls will event work... ordering is still a problem :(

Unfortunately, RC4 does not solve the ordering problems I still find that user_vacations_kludge, salary_divisor, salary_multiplier and im_projects_level do not exist (ordering problem).

If I understand the new dump format correctly, the OID in the comments before each entry is OID as it is in the DB. What is weird is that the 'create function OIDs' are _higher_ than the views, I haven't checked the load-data-model files yet, but it seems very unlikely to me that the views were originally defined before the SPs...

Still checking.

I have been to the pgsql-hackers list. The pg_dump problem is very reproducible and should be fixed ASAP, is their opinion.

The 'sad' news is: It will not make it into 7.1, as that has been released this night (mine anyway). People running RC4: don't bother upgrading, they only changed one string in the python arena (removed 'BETA' from something.

The pg_dump problem, if I understand correctly, exists because the view is dumped when the related table (based on OID) is dumped. So the view OID is not used, but the table/relation OID instead. They will fix this in 7.1.1 which 'The Hermit Hacker' scheduled for may 1st. I would suggest not upgrading to 7.1 for production 3.2.x systems, unless you REALLY need the features, as you will run into restore problems... waiting 15 more days should not be a problem, me thinks.

On the bright side: 7.1 is much, much faster in my limited experience to date. The restore took only a fraction of the time it took on 7.0... jummy!