Forum OpenACS Q&A: Postgresql 7.3 packaged...
> Functions/Identifiers > By default, functions can now take up to 32 parametershttp://www.ca.postgresql.org/ftpsite/README.v7.3
Some of the many changes in PostgreSQL 7.3 broke compatibility with OpenACS. Here's a list of incompatibilities I've found so far and how to fix them:
- bitfromint4 and bittoint4 have been dropped in favour of the more comon bit and int4. Fix: replace bitfromin4 with "bit" (including the quotes!) and replace bittoint4 with "int4".
- Type casting has been overhauled. Implicit integer to varchar type casts have become a thing of the past. Fix: create a copy of the declaration of each Pl/PGSQL function that accepts integers in a varchar parameter. Change the type of that parameter in the copy to integer. Perform an explicit cast in the copy and call the original.
- Another type cast casualty is the implicit casting from timestamptz to timestamp. Timestamp (without time zone) has become the default timestamp format. However current_timestamp still returns timestamptz values. Fix: replace timestamp parameters in PL/PGSQL functions with timestamptz.
- The function int_to_tree_key needs a explicit cast as the new "bit" function doesn't accept double precision parameters. The outcome of -2^31 + p_intkey computation is of double precision and needs to be casted to int4 like so:
"bit"(cast (-2^31 + p_intkey as int4))
That is it so far. OpenACS is still not working with PG 7.3. To be continued....
A another couple of PostgreSQL 7.3 and OpenACS incompatibilities:
- Datetime and timespan have been dropped. They were already depreciated in 7.0. Fix: use timestamp and interval instead.
- 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.
Functions that accept integers into a varchar are just plain wrong, right? How many such functions exist?
We can expect 7.4 to introduce even more incompatibilities :)
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)
the need to do bitstring casts like that is a bug in postgres that by the sound of it appeared for 7.3 and will be fixed for 7.4 (but not before since it will require an initdb)... annoying...
reference here - http://archives.postgresql.org/pgsql-hackers/2002-10/msg00568.php
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.
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.
CVS HEAD != OpenACS 4.6. To use PG 7.3.1 You'll have to use the HEAD.