Description of problem: I'm using a spacewalk instance that has been upgraded step by step from v1.2 to v1.5. As part of the 1.4 to 1.5 upgrade the database was migrated from oracle to postgres as the oracle instance was out of space. Starting with v1.5 I have noticed errors when registering a new host where the activation key has system groups listed, and deleting hosts that were included in the upgraded data. Systems added after the 1.5 upgrade can be deleted. Version-Release number of selected component (if applicable): 1.5 How reproducible: 100% for me. Steps to Reproduce: 1. Have a spacewalk instance pre 1.5 on oracle with registered systems. 2. Follow the update to 1.5 and migrate to postgres. 3. Old systems can not be deleted. Seperately activation keys with registered groups is broken, it appears to be the same problem in both instances. In both cases the error is a call to the non existent routine: HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT update_perms_for_user( $1 ) CONTEXT: PL/pgSQL function "update_perms_for_server_group" line 22 at PERFORM and The following exception occurred while executing this request: POST /rhn/systems/details/DeleteConfirm.do Exception: javax.servlet.ServletException: ERROR: function update_perms_for_user(numeric) does not exist at org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:535) This procedure does not exist in the DB and the definition in main.sql would throw an exception anyway: create or replace function update_perms_for_server_group ( server_group_id_in in numeric ) returns void as $$ begin raise exception 'Stub called, must be replace by .pkb'; end; $$ language plpgsql; Adding the stored procedure as defined in /etc/sysconfig/rhn/postgres/main.sql doesn't solve the problem, it simply prints the error "Stub called..." and errors out. The second issue of not being able to register new systems using an activation key occurs when there are 'group' listed in the activation key and results in the 'rhn_reg' returning the error 'Internal Server Error'. Removing the groups from the activation key allows the systems to be registered, and you can manually assign the groups after registration. Any suggestions or is this simply 'not supported' in postgres yet ? Cheers Jason.
Note - you also can't remove systems from groups. content-type: text/xml user-agent: rhn.rpclib.py/$Revision$ x-client-version: 1 x-info: RPC Processor (C) Red Hat, Inc (version $Revision$) x-rhn-client-capability: packages.verify(1)=1,packages.extended_profile(2)=1,configfiles.base64_enc(1)=1,reboot.reboot(1)=1,configfiles.deploy(1)=1 ,caneatCheese(1)=1,packages.rollBack(1)=1,configfiles.mtime_upload(1)=1,configfiles.diff(1)=1,packages.update(2)=2,script.run(1)=1,configfiles.upload(1)=1, packages.runTransaction(1)=1,packages.verify(1)=1,packages.extended_profile(2)=1,configfiles.base64_enc(1)=1,reboot.reboot(1)=1,configfiles.deploy(1)=1,can eatCheese(1)=1,packages.rollBack(1)=1,configfiles.mtime_upload(1)=1,configfiles.diff(1)=1,packages.update(2)=2,script.run(1)=1,configfiles.upload(1)=1,pack ages.runTransaction(1)=1 x-rhn-transport-capability: follow-redirects=3 x-transport-info: Extended Capabilities Transport (C) Red Hat, Inc (version $Revision$) x-up2date-version: 1.5.16-1.el5 Extra information about this error: SQL Error generated: ('function nvl2(numeric, integer, integer) does not exist at character 28\nHINT: No function matches the given name and argument type s. You might need to add explicit type casts.\n', 0, '\n select sc.channel_id, NVL2(c.parent_channel, 0, 1) is_base_channel\n from rhnServerChannel sc, rhnChannel c\n where sc.server_id = %(server_id)s\n and sc.channel_id = c.id\n') Exception Handler Information Traceback (most recent call last): File "/usr/lib/python2.4/site-packages/spacewalk/server/apacheRequest.py", line 123, in call_function response = apply(func, params) File "/usr/share/rhn/server/handlers/xmlrpc/queue.py", line 329, in get ret = self.__getV2(action) File "/usr/share/rhn/server/handlers/xmlrpc/queue.py", line 87, in __getV2 result = method(self.server_id, action['id'], dry_run) File "/usr/lib/python2.4/site-packages/spacewalk/server/action/activation.py", line 49, in schedule_deploy action_id, scheduler=None) File "/usr/lib/python2.4/site-packages/spacewalk/server/rhnServer/server_kickstart.py", line 375, in schedule_rhncfg_install capability) File "/usr/lib/python2.4/site-packages/spacewalk/server/rhnServer/server_kickstart.py", line 447, in _subscribe_server_to_capable_channels h.execute(server_id=server_id) File "/usr/lib/python2.4/site-packages/spacewalk/server/rhnSQL/sql_base.py", line 163, in execute return apply(self._execute_wrapper, (self._execute, ) + p, kw) File "/usr/lib/python2.4/site-packages/spacewalk/server/rhnSQL/driver_postgresql.py", line 269, in _execute_wrapper raise rhnSQL.SQLStatementPrepareError(0, str(e), self.sql) SQLStatementPrepareError: ('function nvl2(numeric, integer, integer) does not exist at character 28\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\n', 0, '\n select sc.channel_id, NVL2(c.parent_channel, 0, 1) is_base_channel\n from rhnServerCh annel sc, rhnChannel c\n where sc.server_id = %(server_id)s\n and sc.channel_id = c.id\n')
Bug from comment #1 has been fixed by commit 0fac2b9fc95564ea170f7cbe338fe372bb9c32be 732325 - nvl2 replace with ANSI case
As for definition of update_perms_for_user() in coment #0 - it's the same issue as described in https://www.redhat.com/archives/spacewalk-list/2011-September/msg00079.html. It's a stub which comes from rhn_cache.pks and just bellow (in /etc/sysconfig/rhn/postgres/main.sql) is the "real" definition which comes from rhn_cache.pkb. So it looks like an installation issue.
Spacewalk 1.6 has been released.