Keeping auditor tables up to date after source tables change

Bug #1768869 reported by Josh Stompro
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Fix Released

Bug Description

EG 2.10 upgrading to EG 3.1

I noticed that the new fields for tracking merge info and visibility were missing from our auditor.biblio_record_entry table after a test upgrade from 2.10 to 3.1.

Should the upgrade process address keeping existing auditor tables up to date? Or perhaps it just warrants a release note entry that it is something that needs to be dealt with?


Tags: pullrequest
Changed in evergreen:
status: New → Confirmed
Revision history for this message
Jeff Davis (jdavis-sitka) wrote :

There are 10 audited tables in a stock EG install. I think it's reasonable to expect schema updates to those 10 tables to be accompanied by SQL for making the corresponding changes to the auditor tables.

Maybe we want a pgTAP test that compares auditor vs audited?

Revision history for this message
Mike Rylander (mrylander) wrote :

As it happens, we have a function that will go through and update auditor tables. Perhaps we should simply run auditor.update_auditors() at the end of every version upgrade script, just to be safe?

Revision history for this message
Josh Stompro (u-launchpad-stompro-org) wrote :

Mike, that sounds like a great solution. I added that command "select auditor.update_auditors();" to the end of a test db upgrade and it fixed the problem.

What would be a good way to get this included in the next version upgrade scripts for 3.1 and 3.2? I'm not familiar with how the version upgrade sql files get created. If there are a set of instructions for creating those, them maybe that just needs to be updated? Is there a script that builds the version upgrade from the upgrade/xxx*.sql files?


Revision history for this message
Mike Rylander (mrylander) wrote :

The version upgrade script is created during the release bundling process by a script called, unsurprisingly, make_release. I think something like this would work:

diff --git a/build/tools/make_release b/build/tools/make_release
index c3fd2ed..2f00042 100755
--- a/build/tools/make_release
+++ b/build/tools/make_release
@@ -259,6 +259,8 @@ if [ "$PREV_BRANCH" != "PACKAGE" ]; then
                 cat Open-ILS/src/sql/Pg/upgrade/$NUM.* 2>/dev/null | grep -v '^\s*\(BEGIN\|COMMIT\);\s*$' >> $UPGRADE_FILE
             echo "COMMIT;" >> $UPGRADE_FILE
+ echo "" >> $UPGRADE_FILE
+ echo "SELECT auditor.update_auditors();" >> $UPGRADE_FILE
             MAYBE_DUPES=`grep -oP 'CREATE (OR REPLACE )?FUNCTION +\K[^ ]*(?= *\()' $UPGRADE_FILE | sort | grep -P '^(.*)\n\1$' | sort -u`
             if [ -n "$MAYBE_DUPES" ]; then
                 echo ""

Revision history for this message
Josh Stompro (u-launchpad-stompro-org) wrote :

Mike, I put your example into a branch to make it easier for someone to test it out. I'm not sure how to go about using the make_release script, so I haven't tested it.


I'm going to add the pullrequest tag just so it has more visibility, but if anyone feels like that isn't appropriate please update the tags.

tags: added: pullrequest
Revision history for this message
Galen Charlton (gmc) wrote :

Pushed to master for inclusion in 3.4. Thanks, Josh!

I've elected not to backport it further, as we shouldn't expect much if any in the way of schema updates of audited tables in 3.3 and later.

Changed in evergreen:
milestone: none → 3.4-beta1
importance: Undecided → Medium
status: Confirmed → Fix Committed
Galen Charlton (gmc)
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers