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

I've run "manually" (I mean psql -f packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7*). And I did it carefully IN ORDER. I see only 2 errors (it seems not important to my installation):

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

Collapse
Posted by Gustaf Neumann on
The first error is harmless, the second one is not essential either, but comes from to changes in postgres 9. You can fix this by loading the definition of the failing function "get_func_definition" from packages/acs-kernel/sql/postgresql/postgresql.sql

connect to the db with psql, and paste the following command, and this is fixed as well.

--
-- procedure get_func_definition/2
--
CREATE OR REPLACE FUNCTION get_func_definition(
   fname varchar,
   args oidvector
) RETURNS text AS $$
DECLARE
        nargs           integer default 0;
        v_pos           integer;
        v_funcdef       text default '';
        v_args          varchar;
        v_one_arg       varchar;
        v_one_type      varchar;
        v_nargs         integer;
        v_src           text;
        v_rettype       varchar;
BEGIN
        select proargtypes, pronargs, number_src(prosrc), 
               (select typname from pg_type where oid = p.prorettype::integer)
          into v_args, v_nargs, v_src, v_rettype
          from pg_proc p 
         where proname = fname::name
           and proargtypes = args;

         v_funcdef := v_funcdef || '
create or replace function ' || fname || '(';

         v_pos := position(' ' in v_args);

         while nargs < v_nargs loop
             nargs := nargs + 1;
             if nargs = v_nargs then 
                 v_one_arg := v_args;
                 v_args    := '';
             else
                 v_one_arg := substr(v_args, 1, v_pos - 1);
                 v_args    := substr(v_args, v_pos + 1);
                 v_pos     := position(' ' in v_args);            
             end if;
             select case when nargs = 1 
                           then typname 
                           else ',' || typname 
                         end into v_one_type 
               from pg_type 
              where oid = v_one_arg::integer;
             v_funcdef := v_funcdef || v_one_type;
         end loop;
         v_funcdef := v_funcdef || ') returns ' || v_rettype || E' as ''\n' || v_src || ''' language ''plpgsql'';';

        return v_funcdef;

END;
$$ LANGUAGE plpgsql stable strict;

glad we have a happy end!
-g
I've done it without errors.

Thanks 😉