Forum OpenACS Q&A: Postgresql 7.3 packaged...

http://archives.postgresql.org/pgsql-hackers/2002-11/msg01057.php

> Functions/Identifiers
> By default, functions can now take up to 32 parameters

...finally

A another couple of PostgreSQL 7.3 and OpenACS incompatibilities:

1. Datetime and timespan have been dropped. They were already depreciated in 7.0. Fix: use timestamp and interval instead.
2. Adding integers to timestamps doesn't work anymore. Fix: use an interval instead of an integer. E.g.:

   now() + (365 *20)

   becomes

   now() + interval '20 years'

With these changes I'm now able to install OpenACS core and several other packages. Let's see how the stand up in actual use.

Most of the changes are backwards compatible except for:

• "bit" instead bitfromint4 and "int4" instead of bittoint4. The functions "bit" and "int4" are new to PG 7.3.
• Explicit casting of integers to varchars in PL/PGSQL functions is not directly incompatible with prior PG versions but certainly not an elegant solution.

/Bart

There are a few of functions that accept integers as a varchar parameter. Here's the list:

• acs_attribute__create_attribute (acs-kernel)
• content_module__new (cms)
• cm_form_widget__set_attribute_param_value (cms)

/Bart

Patches to make OpenACS PG 7.3 compatible have been applied to the OpenACS CVS HEAD. Mind you you'll still need to apply the following patch to PG 7.3 itself:

*** src/backend/optimizer/plan/planner.c.orig    Tue Sep 24 14:38:23 2002
--- src/backend/optimizer/plan/planner.c    Thu Dec  5 16:32:21 2002
***************
*** 330,339 ****
              * nothing will happen after the first time.  We do have to be
              * careful to copy everything we pull up, however, or risk
              * having chunks of structure multiply linked.
              */
             subquery->jointree = (FromExpr *)
                 pull_up_subqueries(subquery, (Node *) subquery->jointree,
!                                    below_outer_join);
 
             /*
              * Now make a modifiable copy of the subquery that we can run
--- 330,343 ----
              * nothing will happen after the first time.  We do have to be
              * careful to copy everything we pull up, however, or risk
              * having chunks of structure multiply linked.
+              *
+              * Note: 'false' is correct here even if we are within an outer
+              * join in the upper query; the lower query starts with a clean
+              * slate for outer-join semantics.
              */
             subquery->jointree = (FromExpr *)
                 pull_up_subqueries(subquery, (Node *) subquery->jointree,
!                                    false);
 
             /*
              * Now make a modifiable copy of the subquery that we can run
***************
*** 513,518 ****
--- 517,536 ----
      * quals of higher queries.
      */
     if (expression_returns_set((Node *) subquery->targetList))
+         return false;
+ +     /*
+      * Don't pull up a subquery that has any sublinks in its targetlist,
+      * either.  As of PG 7.3 this creates problems because the pulled-up
+      * expressions may go into join alias lists, and the sublinks would
+      * not get fixed because we do flatten_join_alias_vars() too late.
+      * Eventually we should do a complete flatten_join_alias_vars as the
+      * first step of preprocess_expression, and then we could probably
+      * support this.  (BUT: it might be a bad idea anyway, due to possibly
+      * causing multiple evaluations of an expensive sublink.)
+      */
+     if (subquery->hasSubLinks &&
+         contain_subplans((Node *) subquery->targetList))
         return false;
 
     /* 

My advise is to wait for a PG 7.3.1 release and the release of OpenACS 4.6 after which the 4.6 branch will be merged to the CVS HEAD.

/Bart

checkout a copy of OpenACS from the CVS HEAD and download PostgreSQL 7.3.1 from the PostgreSQL ftp server and you are good to go.

/Bart

CVS HEAD != OpenACS 4.6. To use PG 7.3.1 You'll have to use the HEAD.

/Bart