[OPEN-ILS-DEV] Deleting the data in the auditor schema

[Duimovich, George]

For the benefit of the list, we earlier got a great tip from ESI support that relates to this.

If you're doing global updates and are *certain* you'd like to avoid bulking up the BRE associated auditor table, you can disable/enable the trigger before and after your update.

Example: 

--disable auditor trigger for BRE
ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update_trigger;
--run your update
UPDATE biblio.record_entry 
	set marc=REPLACE(marc, 'bookstore.cfs.nrcan.gc.ca/index.htm', 'cfs.nrcan.gc.ca/publications/')
	where marc like '%bookstore.cfs.nrcan.gc.ca/index.htm%' and deleted = 'false';
--re-enable trigger for BRE
ALTER TABLE biblio.record_entry ENABLE TRIGGER audit_biblio_record_entry_update_trigger;

Sometimes we forget to remove the trigger for trivial global updates so removing unnessary rows in the auditor table is very useful, but we'd like to go back to remove some bloat incurred from before we learned about the 'DISABLE TRIGGER' trick...

The auditor tables are a great feature but depending upon the global update, we sometimes prefer to backup the data to be changed outside of the database rather than bloat the BRE auditor table. So we might use our wiki or pg_dump or note what record id's were impacted by a particular update instead etc.).

George Duimovich
NRCan Library / Bibliothèque de RNCan


-----Original Message-----
From: open-ils-dev-bounces at list.georgialibraries.org [mailto:open-ils-dev-bounces at list.georgialibraries.org] On Behalf Of Galen Charlton
Sent: October 31, 2011 13:28
To: open-ils-dev at list.georgialibraries.org
Subject: Re: [OPEN-ILS-DEV] Deleting the data in the auditor schema

Hi,

On 10/31/2011 01:27 PM, Mike Rylander wrote:
> Just a lack of troubleshooting information that can be handy.  Though, 
> auditor tables are rarely used, and the logs are usually enough.

And in the case of doubt (or external requirements that the the data be retained), you can always use pg_dump to back up the auditor schema to a file before clearing out the auditor.*_history tables.  You can also consider keeping just the past few months of auditor rows.

Regards,

Galen
--
Galen Charlton
Director of Support and Implementation
Equinox Software, Inc. / The Open Source Experts
email:  gmc at esilibrary.com
direct: +1 770-709-5581
cell:   +1 404-984-4366
skype:  gmcharlt
web:    http://www.esilibrary.com/
Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org