Re: [opennms-discuss] Events database purging, was Re: DataBase Sizing

[Den B. <rad...@ya...>]

Oops...

Pardon me. I intent to purge events table only.
I still need outages record and notifications records.

Your query logically right, but why my postgresql
return null record?

delete from events where eventid not in (select
svclosteventid from
outages) and eventid not in (select
svcregainedeventid from outages);

Postgresql can answer query correctly:
1. select svclosteventid from outages
2. select svcregainedeventid from outages
3. select svclosteventid from outages UNION select
svcregainedeventid from outages
4. select * from events where eventid in (select
svclosteventid from outages) and eventid not in
(select svcregainedeventid from outages);

BUT, postgresql CAN NOT answer the query sql 5
correctly (Postgresql return NULL records). 

5. select * from events where eventid not in (select
svclosteventid from outages) and eventid not in
(select svcregainedeventid from outages);

Pls advice me.

Big Thx.

--- wro...@do... wrote:
> Attached is some SQL to delete one or more nodes,
> and all associated rows
> from other tables.  Edit the first select statement
> to set up your
> criteria.
> 
> I run this with the command line:
> 
> psql opennms < deleteNode.sql
> 
> I plan to make this into a Perl or shell script, to
> pass the criteria as
> parameters.
>  (See attached file: deleteNode.sql)
> 
> Wrolf Courtney
> Donovan Data Systems, Inc.
> (212) 633-5470
> 
> 
> 
>                                                     
>                                                     
>                  
>                     Den Bei                         
>                                                     
>                  
>                     <rad...@ya...>        To:
>     di...@li...                       
>                  
>                     Sent by:                     cc:
>     jo...@ad...                                  
>                  
>                     discuss-admin@lists.o       
> Subject:     [opennms-discuss] Events database
> purging, was Re: DataBase  
>                     pennms.org                  
> Sizing                                              
>                      
>                                                     
>                                                     
>                  
>                                                     
>                                                     
>                  
>                     08/01/2002 12:02 AM             
>                                                     
>                  
>                     Please respond to               
>                                                     
>                  
>                     discuss                         
>                                                     
>                  
>                                                     
>                                                     
>                  
>                                                     
>                                                     
>                  
> 
> 
> 
> I have try that query.
> But the result is "DELETE 0". (In fact, i have
> millions events records)
> 
> Thanks.
> 
> --- Jonathan Tullett <jo...@ad...> wrote:
> > Hi,
> >
> > > > If you purge (or attempt to purge) events and
> > they are referenced elsewhere
> > > > (e.g., notifications, outages, etc.), you will
> > either be prevented from
> > > > purging the events (referential integrity) or
> > you will create havoc when
> > > > you attempt to view those
> outages/notifications
> > from the UI and it tries to
> > > > reference the causal event.
> > > i've made a fake event. I don't really care of
> > past events, i just want to del
> > > them all. I just need outages for the
> availibility
> > calculation. So:
> > > - I update svclosteventid and svcregainedeventid
> > outages with that fake
> > > eventid (referential integrity).
> > I had the same problem while trying to purge the
> > 840000 events from our
> > events table, in the end I used the following SQL
> > sucesfully and have
> > not had any problems after using it:
> >
> > delete from events where eventid not in (select
> > svclosteventid from
> > outages) and eventid not in (select
> > svcregainedeventid from outages);
> >
> > I guess this could easily be cron'd for daily
> > purging...
> >
> > --
> > Jonathan Tullett           Office: +44 20 7070
> 0976
> > Systems Administrator
> > ADVFN.com Plc
> > http://www.advfn.com/
> >
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> _______________________________________________
> discuss mailing list (di...@li...)
> To subscribe, unsubscribe, or change your list
> options, go to:
> http://lists.opennms.org/mailman/listinfo/discuss
> 
> 
> 

> ATTACHMENT part 2 application/octet-stream
name=deleteNode.sql



__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com