Forum OpenACS Development: Oracle out of sync

1: Oracle out of sync

Posted by Malte Sussdorff on 02/28/05 10:37 AM

We are facing the situation that the Oracle Version of OpenACS get's out of sync and it does this fairly easily because the current practise seems to be only to commit the PostgreSQL upgrade scripts, not the ones for Oracle.

If this continues we should most likely drop Oracle support unless we certify a release to work with Oracle (and organisations interested in using Oracle need to dig and find out which files from PostgreSQL are missing).

My *preference* though would be to encourage all developers to just copy the postgresql files to the Oracle directory. This will *visibly* break the upgrade for Oracle on upgrade, making it easier for developers to find what is missing.

Last time I proposed this there was adamant disagreement from Dirk, but sadly no better solution has arisen since. And if you look at the CVS commits, there are a couple of commits that do not include an equivalent Oracle file, letting Oracle based versions upgrade smoothly and then choke you to death when trying to use the application. And this sucks, because you *assume* the upgrade worked (no error on upgrade). And if you are not doing very close testing on your staging site before applying the upgrade on Production, you run into big troubles.

Therefore I ask for feedback from the developer community how to fix this. And no, Encouraging everyone to commit Oracle scripts will not work. Most developers do not have access to Oracle even if they care, and even more might not care at all.

Hard as this sounds, we should make it as easy as possible for developers of institutions using .LRN on Oracle to go and fix the scripts, as experience shows that they are the ones who have to do the work anyway.

Oh, and to be fair, if something has been developed on Oracle, you might just as well copy that script over to postgresql and have it break there. My guess is it will be fixed within 24 hours :).

2: Re: Oracle out of sync (response to 1)

Posted by Jeff Davis on 02/28/05 10:59 AM

I would be more inclined to say we add something to raise an error like:

begin
raise_application_error(0,'Upgrade upgrade-5.2.0d8-5.2.0d9 needs to be ported to oracle');
end;
/

and put it in the oracle upgrade directory named properly.

Putting the pg script in means you might end in some wierd (hard to fix) state.

3: Re: Oracle out of sync (response to 1)

Posted by Malte Sussdorff on 02/28/05 11:09 AM

I do agree the raise is better, but if it does not prevent the APM from installing the higher version number, then we'd need to find a better way. Furthermore it involves developers to write additional lines of code instead of just saying:

cp upgrade-5.2.0d8-5.2.0d9 ../../oracle/upgrade

4: Re: Oracle out of sync (response to 1)

Posted by Dave Bauer on 02/28/05 02:54 PM

Let's make sure we are clear that not every upgrade script is relevant for both databases. I have been adding upgrade scripts for PostgreSQL to rename functions to match oracle and add define_function_args calls which are not necessary for Oracle at all. Basically fixing bugs that only exist in one database.

5: Re: Oracle out of sync (response to 1)

Posted by Don Baccus on 02/28/05 06:18 PM

In fact I made an upgrade script for PG yesterday that's just exactly as Dave describes, no Oracle upgrade script is necessary.

Won't the raising of an error, as Jeff describes, trip the APM and force it to fail without completing the upgrade?

6: Re: Oracle out of sync (response to 1)

Posted by Andrew Piskorski on 03/01/05 10:28 PM

Please do not create bogus/broken Oracle upgrade scripts by blindly copying PostgreSQL scripts. Jeff's suggestion makes much more sense.

Also, just how many long-term OpenACS core developers do not have both Oracle and PostgreSQL installed on their development machines? I'm kind of shocked that the number would be much greater than zero.

I've only ever really CVS committed one significant set of changes to OpenACS (the multi-db support), but back then, I most certainly did set up both Oracle and PostgreSQL for my testing, even though I never use PostgreSQL in my own work. Also, I recently installed Oracle 9.2.0.4 (but have not yet published my notes on the process), and frankly, it wasn't bad. Much, much easier than installing 8i.

Doing upgrade scripts for both databases is certainly extra work, so I can understand if/when some developers skip that. But using "I don't have Oracle installed" as an excuse? No way, that doesn't cut it.

7: Re: Oracle out of sync (response to 1)

Posted by Jeff Davis on 03/01/05 10:35 PM

I don't have an oracle instance now. I might set one up
if I get paid work for oracle but all the contracts we have
had have been pg based for a while (in part since we don't
push oracle).

8: Re: Oracle out of sync (response to 1)

Posted by Don Baccus on 03/01/05 11:36 PM

Very few have Oracle, unfortunately. I gave Dave an account on my server so he could debug some of his Oracle changes recently because he doesn't, for instance.

The problem is that Postgres has gotten so good that there's not a strong argument for deploying Oracle as the back end for OpenACS any more. Greenpeace, for instance, is moving Planet (greenpeace.org) to Postgres as part of the Planet II development project.

Frankly, if it weren't for .LRN I think we could get an TIP passed to scrap Oracle support ...

9: Re: Re: Oracle out of sync (response to 8)

Posted by Andrew Piskorski on 03/02/05 10:46 PM

Well, until the day that "Scrap Oracle" TIP gets passed, I will remain shocked that most - as opposed to merely a few - OpenACS developers are too damn lazy to even install both supported RDBMSs on their machines.

For anyone with the technical skills that I suspect, hope, and assume most of the core OpenACS contributors have, and unless my 9i install experience was grossly atypical (which is somewhat possible, as I was using an ancient Red Hat 7.3 box), installing a development-grade Oracle instance probably boils down to no more than an afternoon or two of work, maybe once or twice per year.

What is the biggest obstacle preventing better Oracle support? Install docs for 9i and 10g? What? If there's something I might be able to do to help improve OpenACS Oracle support, I definitely want to at least know what it is...

10: Re: Re: Re: Oracle out of sync (response to 9)

Posted by Andrew Piskorski on 03/02/05 11:37 PM

Also, so many folks seem to think that better OpenACS Apache support is "critical", yet many (I'm now told) are apparently willing to to let OpenACS's long-standing Oracle support gradually wither away and die? Doesn't that seem rather odd?

11: Re: Oracle out of sync (response to 1)

Posted by Jade Rubick on 03/03/05 03:08 AM

Yeah, I don't think dropping Oracle support is a good idea at all unless someone comes up with a foolproof migration plan. And that's not going to happen.

I would never vote for anything this drastic. We have to support upgrades.