[Spacewalk-list] unable to cancel pending actions!

[Grant Gainey]

Hey Andy,

----- Original Message -----
> Grant --
> 
> Thanks for the offer.  I am testing a workaround of "breaking" OSAD
> connectivity and disabling actions on the client end until the point when
> it is "ok" for the action to get picked up and executed.

Yeah, that'll get you past the problem.

> I'm game for the pgsql option if my testing doesn't work, though.

Here's my current work. Any multi-lingual SQL experts out there care to vet that the Postgresql below accomplishes the same goal as the Oracle SQL below? I know that the Postgresql "works", in that it is syntactically correct - but since I have no data in the broken state, I haven't seen it actually *work*, as in fix the problem.

What we're trying to accomplish is to recursively find and remove all rhnServerActions, that belong to rhnActions, that have *other* rhnActions as their prerequisites, where the prerequisite rhnAction has NO rhnServerActions.  Whew.  In other words, the action at the head of the chain has been cancelled, but the followon actions didn't get to hear about the cancellation.

Andy - you could change the pgres from "delete from" to "select * from", and peek at the results. If they look like the ones that are giving you heartburn, *then* run as a delete.

Also, db-backup strongly recommended (as always when doing db-surgery...)

ORACLE
===
delete from rhnserveraction rsa 
 where rsa.action_id in (
   select a.id
     from rhnaction a
    start with a.id in (
       select a1.id 
         from rhnaction a1 
        where a1.prerequisite is null 
          and not exists (
            select 1 from rhnserveraction sa where sa.action_id = a1.id
          )
    )
    connect by prior a.id = a.prerequisite
 );
===

POSTGRESQL: (8.4)
===
delete from rhnserveraction rsa 
 where rsa.action_id in (
   with recursive rq as (
     select id, prerequisite
     from   rhnaction
     where  id in (
       select a.id 
         from rhnaction a 
        where a.prerequisite is null 
          and not exists (
            select 1 from rhnserveraction sa where sa.action_id = a.id
          )
     )
     union all
     select a1.id, a1.prerequisite
     from   rhnaction a1
     join rq on a1.id = rq.prerequisite
   )
   select id
   from rq
);
===
 
G