Forum OpenACS Development: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99

Collapse
1: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99
Posted by Khy Huang on 03/24/16 06:07 AM
We tried to setup multiple OpenACS on the same Postgresql instance and got an error of

"Database operation "0or1row" failed (exception NSINT, "Query returned more than one row.")

when running the query -

select 1 from pg_class where relname = 'apm_package_versions'

in the file packages/acs-tcl/tcl/site-nodes-procs.tcl .

The pg_class is in the catalog schema and has the information for all object relations. It will return an error if more than one OpenACS is installed on the same Postgresql DB Instance.

Here are the list of files that we modified to include filters for the "current_user":

* acs-tcl/tcl/00-database-procs.tcl
* acs-tcl/tcl/site-nodes-procs.tcl
* tsearch2-driver/tcl/tsearch2-driver-install-procs.tcl
* xotcl-core/tcl/05-db-procs.tcl
* xowiki/tcl/xowiki-utility-procs.tcl

Is this the appropriate fix?

Collapse
2: Re: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99 (response to 1)
Posted by Gustaf Neumann on 03/24/16 10:01 AM
Dear Khy,

if you want to set up multiple OpenACS instances (say the OpenACS instances "oacs-5-9" and "oacs-5-9-custom") against the same PostgreSQL installation (pg speak: "database cluster" [1]) , you have define create different databases [2] in this cluster for every OpenACS instance (the databases might be named e.g. oacs-5-9 and oacs-5-9-custom as well). The name of the db is specified in the startup file of the server.

if you try to run multiple OpenACS instances against the same database (using for both instances the same database instance name), many things will break fundamentally, the changes you indicate above are just the tip of the iceberg.

-g

[1] http://www.postgresql.org/docs/9.4/static/creating-cluster.html
[2] http://www.postgresql.org/docs/9.4/static/app-createdb.html

Collapse
3: Re: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99 (response to 2)
Posted by Khy Huang on 03/24/16 01:33 PM
HI Gustaf,

Thanks for the reply. This is a change from the previous way done with Postgresql 8.x where we created one db and have different users and schemas. Our system administrator went to an Enterprise DB training and it was recommended to go with the one DB approach. Out of curiosity, do you know why OpenACS went into multiple DBs direction ? Thanks.

Regards,
Khy

Collapse
4: Re: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99 (response to 3)
Posted by Gustaf Neumann on 03/24/16 04:12 PM
The decision about using separate databases was done before i got involved with OpenACS. Probably at that time the support for multiple schemata in oracle/pg was bad/not existing, so using different dbs for different instances was a no-brainer. The biggest advantage of the multiple schema approach is imho referential integrity across instances, which is not needed for OpenACS.

Separate dbs makes it easier to migrate/upgrade/save one instance, the security separation is easier, etc. but altogether the pros/cons are somewhat even (see e.g. [1])

-g

[1] https://www.brentozar.com/archive/2011/06/how-design-multiclient-databases/

Collapse
5: Re: OpenACS 5.9.0 , Postgresql 9.4, and Naviserver 4.99 (response to 4)
Posted by Khy Huang on 03/24/16 05:05 PM
Thank You.