[Spacewalk-list] ISE when rhnpushing Solaris Patches

[Pierre Casenove]

Please find attached a patch to correct the following error on page
/rhn/systems/details/packages/patchsets/PatchSetList.do:
.redhat.rhn.common.db.WrappedSQLException: ERROR: function
nvl(timestamp with time zone, timestamp with time zone) does not exist

Which occurs when navigating to System --> Software --> Patch Clusters
and that at least one patch cluster exists.

I've also noticed a potential problem with the name of the patch cluster:
The mpm file that has been generated for the patch cluster is:
patch-cluster-solaris--20120412-1.sparc-solaris-patch-cluster.mpm
(notice the two minus sign in the middle)
In spacewalk GUI, the patch cluster is named:patch-cluster-solaris-

Will all patch clusters be named patch-cluster-solaris- without any problem?

Pierre


2012/7/4 Pierre Casenove <pcasenove at gmail.com>:
> I've rhnpushed the complete patch cluster.
>
> thanks a lot for this fix!
>
>
> 2012/7/4 Jan Pazdziora <jpazdziora at redhat.com>:
>> On Wed, Jul 04, 2012 at 10:05:42AM +0200, Pierre Casenove wrote:
>>> Hello list,
>>> I have generated the 348 mpm files corresponding to the latest Solaris
>>> 10 patch cluster, and I'm trying to rhnpush them.
>>> rhnpush --username=administrator --password=<XXXXXXX>
>>> --channel=solaris-10-sparc-patches --server=http://server/APP --dir
>>> ./mpm
>>> And I get the following error:
>>> Error pushing /home/solaris/mpm/patch-solaris-143513-02-1.sparc-solaris-patch.mpm:
>>> Error 500 (500)
>>> Waiting 1 seconds and trying again...
>>> Internal server error 500 Internal Server Error
>>>
>>> SQLStatementPrepareError: ('column "\'1.4 REV=2006.03.29\'" does not
>>> exist\nCONTEXT:  Error occurred on dblink connection named "at_conn":
>>> could not execute command.\nSQL statement "SELECT
>>> dblink_exec(\'at_conn\',  $1 , true)"\nPL/pgSQL function
>>> "pg_dblink_exec" line 4 at PERFORM\nSQL statement "SELECT
>>> pg_dblink_exec( \'insert into rhnPackageEVR(id, epoch, version,
>>> release, evr) values (\' ||  $1  || \', \' || coalesce(quote_literal(
>>> $2 ), \'NULL\') || \', \' || coalesce(quote_literal( $3 ), \'NULL\')
>>> || \', \' || coalesce(quote_literal( $4 ), \'NULL\') || \', \' ||
>>> evr_t(coalesce(quote_literal( $2 ), \'NULL\'), coalesce(quote_literal(
>>> $3 ), \'NULL\'), coalesce(quote_literal( $4 ), \'NULL\')) || \')\'
>>> )"\nPL/pgSQL function "lookup_evr" line 14 at PERFORM\n', 0, 'select
>>> LOOKUP_EVR(%(epoch)s, %(version)s, %(release)s) id from dual')
>>>
>>> I attached the complete stack trace.
>>>
>>> Any help would be greatly appreciated!
>>
>> Please update your lookup_evr in psql with the version from commit
>> d13b56bc04cb17f7f7cf2ca40b98602547f85adb -- that should fix it.
>>
>> Thank you,
>>
>> --
>> Jan Pazdziora
>> Principal Software Engineer, Satellite Engineering, Red Hat
>>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
-------------- next part --------------
From 983ac9b8a4d72cd65e323ece7e585f14cfd25106 Mon Sep 17 00:00:00 2001
From: Pierre Casenove <pcasenove at gmail.com>
Date: Wed, 4 Jul 2012 11:13:24 +0000
Subject: [PATCH] COALESCE instead of NVL keyword for pgsql compatibility
 ERROR:  function nvl(timestamp with time zone, timestamp with time zone) does not exist at character 56
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 STATEMENT:  SELECT P.id,
                NVL(SA.completion_time,
                    NVL(SA.pickup_time,
                        SA.created)) AS TIMESTAMP,
                ACS.name AS ACTION_STATUS
           FROM rhnActionStatus ACS,
                rhnActionPackage AP,
                rhnPackage P,
                rhnServerAction SA
          WHERE SA.server_id = $1
            AND P.id IN ($2)
            AND AP.action_id = SA.action_id
            AND AP.name_id = P.name_id
            AND AP.evr_id = P.evr_id
            AND ACS.id = SA.status
         ORDER BY SA.completion_time desc, SA.pickup_time desc, SA.created desc

---
 .../common/db/datasource/xml/Package_queries.xml   |    4 ++--
 1 files changed, 2 insertions(+), 2 deletions(-)

diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index 59d3ce5..dc40fd9 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -1085,8 +1085,8 @@ ORDER BY  UPPER(pn.name)
   </query>
   <elaborator params="sid" multiple="t">
 SELECT P.id,
-       NVL(SA.completion_time,
-           NVL(SA.pickup_time,
+       COALESCE(SA.completion_time,
+           COALESCE(SA.pickup_time,
                SA.created)) AS TIMESTAMP,
        ACS.name AS ACTION_STATUS
   FROM rhnActionStatus ACS,
--
1.7.4.1