Forum OpenACS Q&A: Problem in running OpenACS-3.2.5 with PostgreSQL-7.2.2

Collapse
1: Problem in running OpenACS-3.2.5 with PostgreSQL-7.2.2
Posted by Abhay Kulkarni on 04/23/04 06:43 AM
Hi All ! In our setup, we are running OpenACS-3.2.5 on Red Hat 7.1 with PostgreSQL-7.1.3. And it runs just fine, in development as well as production. We decided to upgrade to Red Hat 8 which has shipped PostgreSQL-7.2.2 with it. While running the index.adp page, we get the message as "Connection to server broken" with following log message NOTICE: Error occurred while executing PL/pgSQL function sec_rotate_last_visit NOTICE: line 11 at SQL statement [22/Apr/2004:16:41:23][16947.40966][-conn0-] Error: Ns_PgExec: result status: 7 message: ERROR: value too lon g for type character(1) [22/Apr/2004:16:41:23][16947.40966][-conn0-] Error: dbinit: error(localhost::pbnic,ERROR: value too long for type character(1) ): 'select sec_rotate_last_visit(1, 1082632283)' [22/Apr/2004:16:41:23][16947.40966][-conn0-] Error: Filter sec_read_security_info returned error #1: Database operation "1row" failed (exception NSDB, "Query was not a statement returning rows.") [22/Apr/2004:16:41:23][16947.40966][-conn0-] Error: tclop: invalid return code from filter proc 'Critical filt er sec_read_security_info failed.': must be filter_ok, filter_return, or filter_break NOTICE: Error occurred while executing PL/pgSQL function sec_rotate_last_visit NOTICE: line 11 at SQL statement [22/Apr/2004:16:41:26][16947.40966][-conn0-] Error: Ns_PgExec: result status: 7 message: ERROR: value too lon g for type character(1) [22/Apr/2004:16:41:26][16947.40966][-conn0-] Error: dbinit: error(localhost::pbnic,ERROR: value too long for type character(1) ): 'select sec_rotate_last_visit(3, 1082632286)' [22/Apr/2004:16:41:26][16947.40966][-conn0-] Error: Filter sec_read_security_info returned error #1: Database operation "1row" failed (exception NSDB, "Query was not a statement returning rows.") [22/Apr/2004:16:41:26][16947.40966][-conn0-] Error: tclop: invalid return code from filter proc 'Critical filt er sec_read_security_info failed.': must be filter_ok, filter_return, or filter_break Can anybody help please ? Thank you, Regards. p.s. I am selecting html format, but the page is showing plain text.
Collapse
2: Re: Problem in running OpenACS-3.2.5 with PostgreSQL-7.2.2 (response to 1)
Posted by Richard Hamilton on 04/23/04 03:56 PM
There have been a number of changes to PostgreSQL over the last few versions which have required work on the OpenACS to accommodate.

Not all versions of OpenACS support all PostgreSQL versions (check out the version compatibility matrix here)

There are also quite extensive posts on the forum discussing upgrade issues, the most recent of which centred around changes to the way PostgreSQL represents/returns time stamps. There have also been issues with the backup and restore program pg_dump in previous versions of PostgreSQL not properly restoring certain function definitions.

This error appears to be related to a change in a data type in PostgreSQL which is breaking one of the queries in your openacs (others in this community will know immediately which change to PostgreSQL caused this and there is probably an upgrade doc somewhere on this site if you search for it).

The basic conclusion for you will I think be that it is too much work to try to single handedly fix all the upgrade bugs on your installation. So your options probably are:

1) Download the PostgreSQL 7.2.2 source code from http://www.postgresql.org and compile it to replace the version in RedHat8.0 (I install RedHat without Postgres and compile from source on my system). Very little work and avoids big changes to your existing site.

or..

2) Upgrade your site to OpenACS 4.6.3 (or even 5.0.4). This will still require you to compile a more recent version of Postgres (see the matrix). You might be able to upgrade to postgres 7.2.3 using the RedHat up-2-date program, but compiling Postgres is easy (just a couple of gotchas which are covered on the forum). This option may also be a lot of work because the upgrade to the new generation OpenACS might be painful.

I hope this helps.
Regards Richard