Forum OpenACS Q&A: General Comments Broken?

Collapse
Posted by Richard Hamilton on
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

Collapse
Posted by Richard Hamilton on
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

Collapse
Posted by Richard Hamilton on
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

--------+--------------------------------------------+-------------------+-----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
--------------------
public | apm__get_value | character varying | character varying, character varying
public | apm__get_value | character varying | integer, character varying

public | apm__id_for_name | integer | character varying, character varying
public | apm__id_for_name | integer | integer, character varying

public | apm__register_parameter | integer | integer, character varying, character varying, character varying
, character varying, character varying, character varying, character varying, integer, integer
public | apm__register_parameter | integer | integer, character varying, character varying, character varying
, character varying, character varying, character varying, integer, integer

public | apm__set_value | integer | character varying, character varying, character varying
public | apm__set_value | integer | integer, character varying, character varying

public | apm_package_version__remove_dependency | integer | character varying, character varying, integer
public | apm_package_version__remove_dependency | integer | integer

public | apm_package_version__remove_interface | integer | character varying, character varying, integer
public | apm_package_version__remove_interface | integer | integer

...

(55 rows)

Collapse
Posted by Richard Hamilton on
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)
Collapse
Posted by Richard Hamilton on
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

Collapse
Posted by Gustaf Neumann on
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

Collapse
Posted by Richard Hamilton on
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

Collapse
Posted by Richard Hamilton on
Oops - Sorry!! Please disregard last comment! I didn't say anything at all! :-O

R.