Forum OpenACS Development: FYI: Errors upgrading ]po[ from OpenACS 5.9.0 to 5.9.1


We're trying here to upgrade ]project-open[ to OpenACS 5.9.1 (because 5.10 apparently requires the latest NaviServer...). We are using NaviServer/4.99.8 as a base, in order to be compatible with the infrastructure (NaviServer and Database) of ]po[ V5.0.

We've got several errors during the upgrade:

1. /acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql
We had to add two hard "drop function" calls here, because the "create or replace" calls failed due to incompatible return types of the functions:

drop function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean,boolean,varchar,varchar,boolean,varchar,varchar,varchar);

drop function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean);

2. acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d3-5.7.0d4.sql did not run previously.
I don't know why this happened, but this issue left the table acs_datatypes with missing columns that lead to several other errors.

3. ]po[ zombie users in /packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.0-5.9.1d1.sql

There were zombie entries in acs_objects from failed "nuke" operations or whatever. We've added the following line in order to avoid upgrade errors:

delete from acs_permissions where grantee_id in (select object_id from acs_objects where object_type = 'user' and object_id not in (select party_id from parties));

I understand that's more our problem than OpenACS...

4. XoWiki failed upgrade due to ]po[ zombie entries in cr_items.

XoWiki tried to add some CR constraint during startup which failed due to zombie entries in cr_items. We had to run the following SQL in order to fix this:

delete from acs_objects where object_id in (
select object_id
from acs_objects
where object_type = 'content_item' and
object_id not in (select item_id from cr_items)

Now OpenACS 5.9.1 seems to run fine. We'll now add some CSP policies in order to deal with XSS with return_url etc.


What makes you think that 5.10 requires the latest NaviServer?

You did not make an upgrade from 5.9.0 to 5.9.1, but from 5.7* to 5.9.1. It looks to me as if the po 5.9.0 was just a partial OpenACS 5.9.0, since apparently some upgrade scrips required for 5.9.0 were skipped - probably causing what you call "zombie items".

Glad, that 5.9.1 still works.


what makes you think

It uses try - catch, which is not in NaviServer 4.99.8 and ns_parseurl which also only appears considerably later. Then I stopped checking and went for 5.9.1, which already includes CSP (which is what I was looking for).


Yeah, something went wrong a long time ago. I'm glad that xowiki works, that was the most difficult one...


It uses try - catch, which is not in NaviServer 4.99.8 ...

"try" has nothing to do with NaviServer, it is part of Tcl 8.6 (released in 2013)

If for whatever reason you have to use Tcl 8.5, you can use the "try" package in tcllib for Tcl 8.5 upward compatibility.

all the best -g

Hi Frank, at this point I am wondering what prohibits using the latest Naviserver and the latest TCL version, as both have been around for some time.

Additionally, I am getting concerned about reliance on PG9. Do you migrate to oacs-5-9-1 CVS version, which according to the compatibility matrix supports PG11?

That move probably requires some changes in PO code, but while you are at it anyway .....



No problem to use the new NaviServer. It's just that ]po[ V5.0 still uses the 4.99.8, and I don't want to tell customers to do yet another upgrade.


We would have to fix TSearch2 for PG 10/11. That's not difficult, it's just another API. The "surface" between ]po[ and TSearch2 is not very large.
However, as with NaviServer, we will still be using CentOS 7 with PG 9 for a while, so from this side there isn't much pressure to do the migration.

I'd estimate half a day to a day for this. We could do this as part of merging your changes into ]po[ "vanilla"?


The following is just for info.

This is ]project-open[ version running on Windows-OpenACS June 2020 version (with the latest of everything).

Have a wonderful Sunday,

Hi Maurizio,

do you have a list of things you needed to do in order to get this working?

Did you use the ]project-open[ dumpfile for this to get started or did you do a fresh install of ]project-open[ on top of your windows system?

I want to update the ]project-open[ docker containers to the latest version, as I do follow your strategy (upgrade to latest packages on a regular basis). Additionally, I'm not entirely sure when e.g. PG9 runs out of maintenance.

Looking forward to your pointers (and if you say "there isn't much to worry about" even the better 😊).


Hello Malte and Frank,
I downloaded around the end of June (29th) the official ]po[ Windows distribution. Then from there I got the DB and the packages.
I put the packages on top of my distribution and used the above DB as starting point.
I run some few upgrades/updates.
To replicate what I did you can get:
1. my distribution:
Once installed, in /naviserver/software/ you can get the naviserver sources I use
2. some material:
containing the modified ]po[ packages and DB.

It is rough material, but I hope it helps.


We would have to fix TSearch2 for PG 10/11.

What do you want to fix? we use it with these versions (with oacs-5-10, but i do not think we had to fix something)


As far as I know, for Tsearch2 in the ]pproject-open[ codebase, the only required modification is to change all calls to "to_tsquery('default', x)" to "to_tsquery(x)"
Tha's it and that's all. A single edit operation.


Hi Maurizio,

Thanks for posting! I knew it would be something like that, but I didn't even look yet ...

]po[ copies the strings to be indexed into a separate table and applies the TSearch2 full-text index on one column of that table. ..


You're welcome Frank.

It's a matter of strategy.

A first strategy corresponds in doing the updates/upgrades every now and then, say every X years. By so doing the number of changes that have to be taken into account is quite considerable. And you have to test all these changes.

Another strategy, the one I've been using for OpenACS, is to always keep up with the developments of the various components/libraries and always make sure that everything works. By so doing, the number of changes that have to be taken into account every single update is very limited (1 or at most 2 at a time). This reduces dramatically the time required to solve the issues related with the change(s).

I might be wrong, but I believe that at the end of the day, the second strategy is cheaper.


Hi Maurizio,

yes, you were right. I only had to remove duplications from tsearch2-driver which made it into intranet-search-pg and ammend the queries as per your instructions. Well.. and load an updated im_search_update function into postgresql before running any update (as there are triggers on cr_items which fire with normal openacs upgrades).

(removed ]project-open[ code..  wrong place )

In case you want to upgrade ]project-open[ to OpenACS 5.10 you will run into the issue of the dropped acs_object_context_index.

You can probably just replace it with calls to acs_privilege_descendant_map and ignore all your manual delete code, but this is something to keep in mind.

Why does po depend on the acs_object_context index? None of the 100+ maintained packages depends on it. The right thing is to use recursive queries in case it is really needed.

]po[ uses acs_object_context_index in the context of "cost center permissions". Cost centers form a tree (the departments and sub-departments of a company...), and any level on the cost center tree can have privileges associated with it. These privileges affect a user's ability to perform CRUD operations on financial documents related to the cost center. Privileges are inherited "down" the cost center hierarchy. So it's sufficient for a system administrator to have an "Admin" privilege on the top CC in order to be able to perform any action (read/write privileges are sub-privileges of "admin") on any financial document type on any level of sub-CC.

To my knowledge this is correct and documented use of the OpenACS permission system.

]po[ has about 20 files that reference acs_object_context_index.

The right thing is to use

So I understand that OpenACS 5.10 breaks this mechanism? I wouldn't be a big thing, but we obviously wouldn't be happy about extra work related to it.

I've read mixed reviews about the performance of recursive queries in PostgreSQL, but I haven't tried yet. There doesn't seem to be any reason to change, right?

I very much value all the work that you and your team put into OpenACS and the community, so we would definitely accept this change and would work around it. However, it would great if you could check compatibility with ]po[ the next time you want to perform an incompatible change.


I seem to recall finding a patch to restore acs_object_context_index for 5.9.1.
Or, maybe I just commented out the code in the upgrade script that removes acs_object_context_index.

We replaced the queries that depended on acs_object_context_index with recursive queries when we upgraded to 5.10.

Well... I do not know if this is the proper place to put some generic considerations... I'll jot them down anyhow.

I believe that what determines the success of a (web) application is:
1. its functions
2. its user interface

]project-open[ success depends heavily on its super duper richness in terms of functions. It actually is so rich that the majority of users will be more than happy to use just its core/common functions. So porting/updating efforts could (and should) concentrate on these core functions and could somehow leave alone, at least in terms of priorities, not so common, exoteric functions, used by a limited set of users.

But, apart from functions, the user interface also matters, and it matters a lot. And it is not just a question of actual user interaction, how the users actually use the system. Unfortunately it is also a matter of fashion. And here I'm afraid both OpenACS and ]project-open[ fall short. They are still kind of stuck to Web 1.0 style. But Web 2.0 has come, and it has gone too... Now it's time of Web 3.0, see for instance
Please stay with me, I know that the majority of this is only "fashion"; but it is "fashion" that matters a lot.

Now offering OpenACS or ]project-open[ on top of Docker is not going to rejuvenate the products, neither porting them to the newest versions of their base software components, libraries - it just makes sure they are going to work in the near future for their current users.

But if we want to make sure OpenACS and ]project-open[ will be still used in a future to come, and by new adopters, we need to work heavily on their user interfaces.

Hi Maurizio,

I'm 100% with you if terns if the ugliness of the ]po[ GUI.
And I believe you are right, the vast majority of customers buy ]po[ because of functionality and not because of prettiness.

I recently somewhere (I don't remember unfortunately) read about ERP software:

- Either it's new, then it has has a sexy GUI according to current fashion trends, but is incomplete.
- Or it's old, ugly, but has everything the business needs.

I believe that's great and really summarizes the dilemma. And ]po[ is clearly on the ugly side.

I really like your proposal, so I've copied the relevant parts to a new post here:

I propose to continue GUI related discussion there and keep this thread for OpenACS 5.9.1 stuff...


Regarding your query for XoWIKI zombie entries this is what I had to run:

# Set context objects to null
update acs_objects set context_id = NULL where context_id in (select object_id from acs_objects left join cr_items on acs_objects.object_id = cr_items.item_id where object_type = 'content_item' and cr_items.item_id is null);

# Delete zombie entries
delete from acs_objects where object_id in (select object_id from acs_objects left join cr_items on acs_objects.object_id = cr_items.item_id where object_type = 'content_item' and cr_items.item_id is null order by object_id desc);

We do use CR for a lot in our ]project-open[ enhancements, so your initial query ran.... too long 😊.