Forum OpenACS Q&A: General Comments Broken?

1: General Comments Broken?

Posted by Richard Hamilton on 09/16/12 03:13 PM

ERROR: column "get_value__parameter_id" does not exist

...when trying to post a message from an xowiki instance.

Does anyone recognise or have any idea what has happened here?

I am working with a checkout of version 5.6.0 dated 20/09/10 and the general-comments is tagged 5-6.

Regards
Richard

2: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/16/12 03:49 PM

This seems to be linked to changes in the stored procedures belonging to the package manager. here is the error:

82.69.2.22 - - [16/Sep/2012:14:29:06 +0100] "POST /comments/comment-add-2 HTTP/1.1" 200 4404 "https://www.oakmasters.com:8440/comments/comment-add?object%5fid=11097&object%5fname=Index+Page&return%5furl=%2fweblog%2f&context%5fid=11097&category="; "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20100101 Firefox/15.0.1" 0.181237 "ad_session_id=11821001%2c667%2c1+%7b708+1347805647+FB208D4C029D4AFF2D8164B12168BB64B69E09A6%7d; ad_user_login_secure=667%2c1347792208%2cBC0AB8504%2c1347792208+%7b733+0+99664A027D18CC497CEDC983FCC78822228E41D9%7d; ad_user_login=667%2c1347792208%2cBC0AB8504+%7b875+0+81627892E6669FE63275506355DF32AEA8764AB0%7d; ad_secure_token=11821001%2c1347792208%2c82%2e69%2e2%2e22+%7b877+1348397008+72D97C7BC5482D360DA92706C1C6641F96DE299E%7d"
[16/Sep/2012:14:29:09][12175.114696][-default:0-] Error: Ns_PgExec: result status: 7 message: ERROR: column "get_value__parameter_id" does not exist
LINE 1: ...es v where v.package_id = $1 and parameter_id = get_value_...
^
QUERY: select attr_value from apm_parameter_values v where v.package_id = $1 and parameter_id = get_value__parameter_id
CONTEXT: PL/pgSQL function "apm__get_value" line 9 at SQL statement
SQL statement "select apm__get_value(package_id, 'SystemURL') from apm_packages where package_key='acs-kernel'"

I have checked the functions in the database and there seem to be duplicate, overloaded function names that I wouldn't expect to see. I will post the postgres output next.

Regards
Richard

3: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/16/12 03:55 PM

Here are the duplicate pl_pgsql functions. Is it possible that an upgrade script has failed to 'create or replace'?

Could such duplicates explain this error?

Which of the functions should be kept?

oakmasters=# \df apm*

List of functions
Schema | Name | Result data type |
Argument data types

...

(55 rows)

4: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/16/12 04:06 PM

It looks as if someone has changed the name of the field package_key to package_id, and the datatype from 'character varying' to 'integer', but has not coded the upgrade script to replace the original function. I presume therefore that the new function is now choking on the field name change.

public | apm__get_value | character varying | character varying, character varying | stable | oakmasters | plpgsql |
|
              : declare
              : get_value__package_key alias for $1;
              : get_value__parameter_name alias for $2;
              : v_parameter_id apm_parameter_values.parameter_id%TYPE;
              : value apm_parameter_values.attr_value%TYPE;
              : begin
              : v_parameter_id := apm__id_for_name (get_value__package_key, get_value__parameter_name);
              :
              : select attr_value into value from apm_parameter_values v
              : where v.package_id is null
              : and parameter_id = get_value__parameter_id;
              :
              : return value;
              :
              : end;

public | apm__get_value | character varying | integer, character varying | stable | oakmasters | plpgsql |
|
: declare
: get_value__package_id alias for $1;
: get_value__parameter_name alias for $2;
: v_parameter_id apm_parameter_values.parameter_id%TYPE;
: value apm_parameter_values.attr_value%TYPE;
: begin
: v_parameter_id := apm__id_for_name (get_value__package_id, get_value__parameter_name);
:
: select attr_value into value from apm_parameter_values v
: where v.package_id = get_value__package_id
: and parameter_id = get_value__parameter_id;
:
: return value;
:
: end;
(2 rows)

5: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/16/12 04:28 PM

I can see that apm_create.sql contains code for both functions, so I presume that this is indeed deliberate. But on that basis I don't know what is going on.

This seems to be an issue with code hiding in acs-kernel.

Richard

6: Re: General Comments Broken? (response to 1)

Posted by Gustaf Neumann on 09/17/12 10:28 AM

The problem is not in General Comments, but in acs-kernel/sql/postgresql/apm-create.sql. The bug was introduced by donb in revision 1.70 of this file (10 Mar 10), and fixed a half year later in revision 1.74 (31 Oct 10, see [1]), but that version never made it to branch 5-6, which was released 22 Sep 10. Apparently, this is a seldomly used function.

i have added just a backport of this change to branch 5-6 of CVS. Hope this helps

best regards
-gustaf neumann

[1] http://cvs.openacs.org/browse/OpenACS/openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql?r2=1.74&r1=1.73

7: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/17/12 11:31 AM

Gustaf,

Thank you very much for the reply. That makes sense. You have an amazing memory! 😊

One thing though, the FishEye code seems to have a spurious semi-colon terminating the query string before the second 'and' clause in both cases.

Regards
Richard

8: Re: General Comments Broken? (response to 1)

Posted by Richard Hamilton on 09/17/12 11:32 AM

Oops - Sorry!! Please disregard last comment! I didn't say anything at all! 😱