Evergreen database Cleanup

action trigger cleanup.sql

-- Warning - some events rely on there being entries in the action_trigger.event table to prevent from sending the same
-- event again.  You need to be using a max event validity delay.

-- Watch out for these event defs
-- Make sure you have a max delay set and don't delete events that are within the bounds of the max_delay.
select *
from
action_trigger.event_definition ated
join 
action_trigger.hook ath on ated.hook=ath.key
where
ated.active=true
and ath.passive=true
and granularity not in ('print-on-demand')

begin;

-- Clean up some event entries for events that we would never need history for
-- Such as self check receipts
delete from action_trigger.event where event_def in (104,10);

--Clean up events that are older than a certain date for other event defs.
delete from action_trigger.event where event_def in () and add_time <= now()-'6 months'::interval;

-- delete action_trigger.event_output rows that are no longer referenced.

-- Table with all the used event_output id numbers
with used_output_id as
(
select * from (
(select
template_output outputid
from
action_trigger.event
where
template_output is not null
group by 1
-- order by 1
)
union
(
select
error_output outputid
from
action_trigger.event
where
error_output is not null
group by 1
-- order by 1
)
union
(
select
async_output outputid
from
action_trigger.event
where
async_output is not null
group by 1
-- order by 1
)
) x
order by 1
)
-- Delete the event_output rows that are no longer referenced from an event.
-- This must not be correct.
delete
from 
action_trigger.event_output ateo
where
not exists (select outputid from used_output_id where ateo.id=outputid)
;

rollback;

Auditor tables cleanup.sql

-- Delete entries from auditor.asset_copy_history where the copy already has over
-- x number of newer versions.

begin;

-- Use window fuction to number each row in descending order, then choose
-- only the rows that are over a certain number. 
with ranked_entries as
(
select 
id,audit_id, audit_time
, row_number() over (PARTITION BY id order by audit_time desc,audit_id desc) as rowcount 
from auditor.asset_copy_history
order by id
)

delete from auditor.asset_copy_history aach
using
ranked_entries x
where x.rowcount > 20
and x.audit_id=aach.audit_id
;


-- Delete from auditor.biblio_record_entry_history where entry is older than date
delete from auditor.biblio_record_entry_history
where
audit_time <= '2015-09-01'::timestamp
;

rollback;