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

Collapse
14: Re: Minor Error in Survey Package after upgrading (response to 13)
Posted by Cesareo Garci­a Rodicio on 11/09/13 01:09 PM
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)

Collapse
16: Re: Minor Error in Survey Package after upgrading (response to 14)
Posted by Gustaf Neumann on 11/09/13 05:40 PM
The upgrade script can change the database in arbitrary ways (adding/deleting tuples/attributes/tables, ... and functions), so there is not much we can actually do to determine automatically, how much was executed. Keeping track of the names of the upgrade scripts could just help in the future, but of course not for not for the past (i.e. your case)...

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

Collapse
17: Re: Minor Error in Survey Package after upgrading (response to 16)
Posted by Cesareo Garci­a Rodicio on 11/09/13 11:05 PM
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 😊

Collapse
18: Re: Minor Error in Survey Package after upgrading (response to 16)
Posted by Patrick Giagnocavo on 11/10/13 12:37 AM
It would take some work, but I would think you could wrap each SQL statement with something that stuffed the SQL statement into a table with a timestamp, and a result code . Then you would have a record of all SQL run during the upgrade process and whether it threw an error or not.
Collapse
19: Re: Minor Error in Survey Package after upgrading (response to 18)
Posted by Cesareo Garci­a Rodicio on 11/11/13 10:20 AM
Yes, as Gustaf said before perhaps this is the better approach. It could be useful in upgrading older versions
Collapse
20: Re: Minor Error in Survey Package after upgrading (response to 14)
Posted by Cesareo Garci­a Rodicio on 11/12/13 09:32 PM
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
21: Re: Minor Error in Survey Package after upgrading (response to 20)
Posted by Gustaf Neumann on 11/12/13 10:48 PM
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
Collapse
22: Re: Minor Error in Survey Package after upgrading (response to 21)
Posted by Cesareo Garci­a Rodicio on 11/13/13 01:14 PM
I've done it without errors.

Thanks 😉