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

Til - I reloaded everything manually. I just queried for function names of 31 characters and built up a huge file of drop/create commands. That's the simple, braindead way and it *seems* to have worked. I did have to drop and recreate one view (acs_messages_latest) but that's it. It would be nice to have a little script to do this automatically.

If there are views in particular you are curious/worried about, let me know and I'll test them out. As I said, I haven't tested thoroughly.

Also, I don't think workaround you mention would help as the problem occurs when the plsql functions which have been truncated are called by their full names from other plsql functions or from xql files. There may be an option to enable PG to look for truncated names in 7.3. I did look at the 7.3 release notes which contain a few upgrade tips but that's not mentioned. There's a link to some additional upgrade tips but the link leads to a 404. I haven't looked any further yet.

In my own work with Oracle, way back when I adopted a naming convention of "*-ph.sql" and "*-pb.sql" to indicate "package header" and "package body" files, since most Oracle PL/SQL code should be in PL/SQL packages. PostgreSQL doesn't have plpgsql packages, but at least when I tweaked the OpenACS Static Pages package, I used the same "*-pb.sql" naming convention for Postgres, to line up with Oracle.

(Note that it is also often useful to have the Oracle PL/SQL package header and body in two separate files, but this is much less important than having the PL/SQL definitions separate from the DML.)

The same goes for views and triggers, incidentally - they should be separate from the DML so you can easily recreate them just by sourcing the file. The general principle here is that stuff which you can simply "create or replace" (stored procedure code, views, triggers) should always be in a separate file from stuff which you can only "create" (tables). This makes the logistics of maintenance and upgrades substantially easier, and there's no drawback. Any more detailed break out of stuff, like PL/SQL package bodies and headers in separate, is just icing on the cake.

Maybe we could come up with some OpenACS recomended standard for this sort of thing and move all the packages towards using it.

I'm willing to work with someone to mold this into a general solution. What else would be needed? How should non-core packages be handled (the only non-core stuff I have installed is photo album and lars-blogger)? Also, if this were to become a general solution, it would probably make more sense to redefine the functions using definitions from a PG 7.3 compatible release, i.e., 4.6.3 (or maybe 4.6.4), and then note that the upgrage path from 7.2 to 7.3 must go through that particular release (I don't think we want to maintain this monstrosity for each release, do we?).

Having a naming convention for breaking out non-DML stuff would be great. Almost every new module I've seen does this for its package code but each seems to use its own naming convention.

And PG 7.2 doesn't support CREATE OR REPLACE VIEW :(

(7.3 does? hope hope)

We're getting *close* but aren't quite to the point where we can CREATE OR REPLACE everything, sadly.  But we can do so for normal functions now since we no longer support PG 7.1  I've been switching over some create scripts when I've had other reasons to visit them.

If anyone wants to go on the warpath to do this for any particular package, feel free ... just keep in mind that it's not kosher to use CREATE OR REPLACE VIEW yet or to use CREATE OR REPLACE on a function that is used in a trigger.

Of course if there are any missing REPLACEs on Oracle packages these should be fixed ASAP.

Is that true for any functions called by a Postgres trigger in any way, or just for the function that defines the body of the trigger?

I created a simple script to read the dump file. And functions that has 31 chars, I try to look for the function name on the oacs dir. Then substitute it.

You can get the script here:

https://openacs.org/storage/file?file_id=117145

This only cover functions. I did saw 2 views that needs un-truncating too, they are:

ad_template_sample_users_sequence
acs_privilege_descendant_map_view

Hope this helps someone.

Also since I just go over the table name, view name and function name. Did I miss anything major? This are the things that are created during restore:

CREATE CONSTRAINT
CREATE FUNCTION
CREATE INDEX
CREATE RULE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TRUSTED
CREATE UNIQUE
CREATE VIEW

1. We use the script to replace all create statements
2. This will only replace the creates. I've noticed some sql queries which use truncated views in the "from" section of their select statements.
3. The problem now arises because the selects will look for the shorter names but Jun's script now created longer names.

Found out the hard way when my RH8.0 got flaky and I was forced to upgrade to RH9.0.

Thanks for using it.  Never thought that it will be used by others who I really know or to openacs.org.

I have put a comment about the views, corrected the regexp and put a link to this forum on the file.

No thanks.  But I am glad it did help someone else.  If anybody wants to improve, its open source.  Modify it. :)

Hey Jun, cool script! Just upgraded three 4.6.3 instances and one from head from PSQL7.2.4 to 7.3.4, with hardly any trouble. I came across another couple of functions that are missed by the script though, from the surveys package. For reference, these are:

• survey_response__initial_respon needs to be replaced survey_response__initial_response_id
• survey_response__initial_user_i needs to be replaced by survey_response__initial_user_id

Sorry I can't offer a patch to the script!

Thank you Jun and Tilmann for doing that - now I know why I really must learn perl!

When I restore the data it was obvious that none of the views had been created and that most of these views were not truncated ones (but depended on cr_xxxx views which were). To make sure that I found them all I modified your script to do views and discovered that the regexp that you have in it is in the default greedy form - which does matter if searching for views.

So here is one possible alternative non greedy regexp for the record :

/^CREATE VIEW "([^"]*)"/

Maybe this will help someone to find other truncated views in some of the more infrequently used packages.

Richard

Actually I think this is the reason why the two functions are missed also.

I don't have commit rights to the cvs - in fact cvs is one of the next things I need to bone up on!

As far as safety is concerned, I am no regexp expert at all but the [^"]* means anything NOT a " any number of times which restricts the match to a function name rather than some thing like :

CREATE VIEW "acs_func_headers" as SELECT get_func_header("

..which will obviously never be found if the view or function name happens to be >31 chars.

I think that should be robust - can't see why not.

R.

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

Since the schema restore worked:

pg_dump -avD yourdatabasename -t cr_revisions -f chooseafilename > chooseafilename.log

This outputs the data as SQL INSERTS. So to restore this you use psql in the normal way.

It's been fixed ... pull postgresql.sql from CVS HEAD. I just changed all the "create function" statements to "create or replace function" - just ignore the table def errors you get when you run it through PSQL.

All seems to work now - fingers crossed.

One extra step that I performed was to use a text editor to search the pg_dump file and replace all occurrences of the truncated function names with the full function names kindly provided by Jun's script. That meant that as soon as I had figured out how to restore the data to my cr_revisions table, all the views were created again automatically.

Time consuming, but a relief now it is done.

Many thanks
Richard

http://cvs.openacs.org/cvs/openacs-4/bin/pg_7.2to7.3_upgrade_helper.pl?view=markup