Forum OpenACS Q&A: Re: PG 7.2->7.3 upgrade gotcha?

Collapse
Posted by Richard Hamilton on
I have spent all day today wading through the pg_dump of an OpenACS 4.6.3 site that I want to move from PostgreSQL 7.2.1 to PostgreSQL 7.4.2 and have now corrected ALL truncated functions and views along with all references to them in the pg_dump.

I have restored the dump into PG7.4.2 and have logged the standard output which looks fine.

However standard error to the console has thrown up some things that I need to ask about.

There are a whole load of lines like this one:

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "surveys_pk" for table "surveys"

which I assume to be harmless.

One in the middle:

ERROR:  missing data for column "content_length"
CONTEXT:  COPY cr_revisions, line 3: "498  496    \N etc...........rest of data row
100%"..."

which worries me.

And a whole load of lines like these:

WARNING:  changing return type of function on_lobs_delete from "opaque" to "trigger"

which from Don's post I suspect is also no problem.

To try to sort out the cr_revisions problem I logged back into PG7.2 abd selected all cr_revisions rows where content_length isnull. There were some, so I did an update to set content_length to 0 where content_length isnull. Query worked fine, dumped the database, ran all of the conversions again to deal with the truncations and........

same error.

Does anyone know how I can sort this out. Maybe drop the constraint that is causing the restore of this table to fail? But which one is it?

Also I see that none of my view have been recreated. Not sure why since I carefully edited all the function references in the dump file. How best to recreate them? I would like to begin with the pa_photosi and pa_photosx views.

Any ideas gratefully received.

Regards
Richard