Forum OpenACS Development: Minor Error in Survey Package after upgrading

Database operation "select" failed
(exception ERROR, "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
")

    while executing
"ns_pg_bind select nsdb0 {
      
	select
	  clob_answer as response,
	  person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::i..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"

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

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=# 

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.

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.

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.

The problem is especially bad, when one upgrades from really old versions, and an early upgrade script fails.

psql:upgrade-5.7.0d3-5.7.0d4.sql:118: ERROR:  update or delete on table "acs_datatypes" violates foreign key constraint "dtype_wdgt_tmpl_dtype_fk" on table "dtype_db_datatypes"
DETAIL:  Key (datatype)=(money) is still referenced from table "dtype_db_datatypes".

invalid command \
psql:upgrade-5.7.0d8-5.7.0d9.sql:76: ERROR:  syntax error at or near "return"
LINE 46:         return v_funcdef;

But (in my dev server) everything seems to be fine. I'm going to do it in my production server and keep an eye on logs.

It seems to be quite safe to upgrade scripts "manual" whenever upgrade in order. It is the first time I do this and I was a bit feary 😊

I had this package (survey) running OK. Thanks so much

Thanks 😉