Forum OpenACS Development: Re: Minor Error in Survey Package after upgrading

An error:
openacs=# select object_id, acs_object__get_attribute(object_id, 'creation_user') from acs_objects order by object_id desc limit 10;
select object_id, acs_object__get_attribute(object_id, 'creation_user') from acs_objects order by object_id desc limit 10;
ERROR:  record "v_rec" has no field "return"
LINE 1: SELECT v_rec.return
               ^
QUERY:  SELECT v_rec.return
CONTEXT:  PL/pgSQL function acs_object__get_attribute(integer,character varying) line 21 at assignment
Collapse
Posted by Gustaf Neumann on
yet another upgrade problem? What do you see, when you do the following (this time, please with the "+")
\x
\df+ acs_object__get_attribute
Well, perhaps something in my upgrading failed (I had to "force" psql one script in kernal). Perhaps some file didn't want to upgrade
openacs=# \x
\x
Expanded display is on.
openacs=# \df+ acs_object__get_attribute
\df+ acs_object__get_attribute
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | acs_object__get_attribute
Result data type    | text
Argument data types | integer, character varying
Type                | normal
Security            | invoker
Volatility          | stable
Owner               | openacs
Language            | plpgsql
Source code         | 
                    | declare
                    |   object_id_in           alias for $1;  
                    |   attribute_name_in      alias for $2;  
                    |   v_table_name           varchar(200);  
                    |   v_column               varchar(200);  
                    |   v_key_sql              text; 
                    |   v_return               text; 
                    |   v_storage              text;
                    |   v_rec                  record;
                    | begin
                    | 
                    |    v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);
                    | 
                    |    v_column     := acs_object__get_attr_storage_column(v_storage);
                    |    v_table_name := acs_object__get_attr_storage_table(v_storage);
                    |    v_key_sql    := acs_object__get_attr_storage_sql(v_storage);
                    | 
                    |    for v_rec in execute 'select ' || quote_ident(v_column) || '::text as return from ' || quote_ident(v_table_name) || ' where ' || v_key_sql
                    |       LOOP
                    |         v_return := v_rec.return;
                    |         exit;
                    |    end loop;
                    |    if not FOUND then 
                    |        return null;
                    |    end if;
                    | 
                    |    return v_return;
                    | 
                    | end;
Description         | 

openacs=# 
Collapse
Posted by Gustaf Neumann on
Your installation is missing the upgrade script: acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d1-5.7.0d2.sql

The problem at hand is a pg8/pg9 incompatibility. pg9 is closer to the standard and does not allow "return" as output column name. The error message from pg is suboptimal.

In case you have still the error.log from the upgrade, i would check, what upgrade scripts were executed and where the upgrade stopped. Then run the missing upgrade scripts manually.

If you don't have the log file anymore, go through the upgrade scripts and check in the database, if these were executed (i.e. pick e.g. an SQL function from the file and check, whether you have it in the db).

Hi Gustaf

Thanks!, it worked 😊 (I run the upgrade function manually).

I've lost error.log (at least I didn't find it) so I'll check this scripts to see if db has all functions in kernel upgrade scripts.

Hi Gustaf

I've been checking some upgrade scripts and I'm a bit confused. It seems that what I find in DB it's differect form upgrade script BUT I don't find big errors (only a few).

So, I'm not completely sure what upgrade scripts has been failed. Without that error.log, how can I know that?

Can I do a "manual upgrade" of each file? I suppose BD might be messed up, right?

Collapse
Posted by Gustaf Neumann on
i think, the upgrade process of a package stops when the first upgrade script fails. Therefore, one has to run the later scripts, which were not executed.

In general it is desirable to have scripts that can be run multiple times without doing harm. i hope, that all upgrade scripts of the kernel have this property. so, i would recommend to make a backup first before running the missing upgrade scripts.

Take care to run the upgrade scripts in the right order.

Collapse
Posted by Gustaf Neumann on
it sounds like a good idea to add a table to the database containing the names of every successfully executed upgrade script with timestamps. Then recoveries from broken upgrades would be much easier and more transparent
It might be. Whatever mechanism that allow user to check what function has be upgraded.

Another Idea could be stop (or prevent) upgrade but I think that's why error.log exists 😊.

My problem now (as a whatever regular user at this point) is that I'm not completely sure about what upgrade script succeed. So I'll try to do it "manual" in my dev server and check if there is some problem.