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

At least in my instalation (version control with git and some custom code) it was easy to check file system source code (I mean, files). I'm having some problems but it is a "merge" problem

But with db functions is hard. Besides of I'm not a pgsql developer, I don't know what versions are installed (I have to check versus upgrade scripts)

I'll run upgrade scripts (psql -f) and report here what I've seen.

It could be a "particular problem" with my installation (but perhaps useful for some old installations that wants to upgrade). 5.8 has been a big improvement for me (performance, cleanup, upgrade postgres and so on)

Yes, I suppose there is no easy solution but it might help to upgrade old versions (but it depends of every customer).

Ideally, I think the best approach would be abort the upgrade based in:
1. Some error (catching error from psql)
2. Some checking (versions or some audits as table approach you've mentioned)

I don't, only some ideas. Any way, perhaps I'm the only one with this problem and it doesn't worth 😊

Yes, as Gustaf said before perhaps this is the better approach. It could be useful in upgrading older versions

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

lib/*.tcl files are sourced on every request; these are not relevant for upgrades. It is true that the developer has to take care about the tcl functions called from the various callbacks (stemming from tcl/*-procs.tcl), but that is a different story than the discussed problem set (upgrading the database and the database functions in connection with failed/halfdone upgrades).