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

Collapse
Posted by David Kuczek on
For folks that don't follow the PG Hackers List: PG 7.3 has been packaged yesterday!

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

Collapse
Posted by Andrei Popov on
While browsing the same, this http://archives.postgresql.org/pgsql-hackers/2002-11/msg01085.php appears to be an interesting item to look at. Anyone given it a try?
Collapse
Posted by James Thornton on
> Functions/Identifiers
> By default, functions can now take up to 32 parameters
http://www.ca.postgresql.org/ftpsite/README.v7.3

...finally

Collapse
Posted by Bart Teeuwisse on

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:

  1. 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".
  2. 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.
  3. 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.
  4. 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....

/Bart

Collapse
Posted by Bart Teeuwisse on

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

Collapse
Posted by Don Baccus on
The bit stuff doesn't bother me too much, because our use is isolated to a few functions, though introducing new functions that need to be quoted because they clash with something else seems somewhat braindead to me.

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 :)

Collapse
Posted by Bart Teeuwisse on

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

Collapse
Posted by russ m on

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

Collapse
Posted by Bart Teeuwisse on

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

Collapse
Posted by Andrei Mitran on
Did you ever get OpenACS working with Postgres 7.3? If so, which version of OpenACS? Any other errors and fixes?

Andrei

Collapse
Posted by Bart Teeuwisse on
Andrei,

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

Collapse
Posted by Andrei Mitran on
What about the 4.6 branch?

Andrei

Collapse
Posted by Bart Teeuwisse on
Andrei,

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

/Bart

Collapse
Posted by tammy m on
Will the postgresql 7.3.1 patches be applied to OACS 4.6 anytime soon?