From 0c159dff820720fb560dcf88ab8cdb0aab2b27b6 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Wed, 14 May 2014 17:03:38 -0400 Subject: [PATCH] Move authority.record_entry trigger changes out of transaction This should prevent 'cannot ALTER TABLE "record_entry" because it has pending trigger events' errors. This is the same approach used in the original 0875 script, but we might also work around the problem with a well-placed SET CONSTRAINTS ALL IMMEDIATE; Signed-off-by: Dan Wells --- .../2.5.3-2.6.0-upgrade-db.sql | 35 +++++++++++-------- 1 file changed, 21 insertions(+), 14 deletions(-) diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.5.3-2.6.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.5.3-2.6.0-upgrade-db.sql index 482bc85841..05f6d67ef8 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/2.5.3-2.6.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/2.5.3-2.6.0-upgrade-db.sql @@ -1,5 +1,19 @@ --Upgrade Script for 2.5.3 to 2.6.0 \set eg_version '''2.6.0''' + +\qecho +\qecho **** NOTICE **** +\qecho 'We are disabling all triggers for authority.record_entry outside the ' +\qecho 'transaction. If this upgrade fails, you may want to double-check that ' +\qecho 'triggers are reactivated, e.g.:' +\qecho 'ALTER TABLE authority.record_entry ENABLE TRIGGER ALL;' +\qecho +ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed; +ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete; +ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901; +ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers; +ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set; + BEGIN; INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.0', :eg_version); @@ -5557,13 +5571,6 @@ END; $func$ LANGUAGE PLPGSQL; -ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed; -ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete; -ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901; -ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers; -ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set; - - SELECT evergreen.upgrade_deps_block_check('0875', :eg_version); ALTER TABLE authority.record_entry ADD COLUMN heading TEXT, ADD COLUMN simple_heading TEXT; @@ -5597,13 +5604,6 @@ ALTER FUNCTION authority.simple_normalize_heading(TEXT) STABLE STRICT; ALTER FUNCTION authority.simple_heading_set(TEXT) STABLE STRICT; -ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed; -ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete; -ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901; -ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers; -ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set; - - SELECT evergreen.upgrade_deps_block_check('0876', :eg_version); @@ -5817,6 +5817,13 @@ $$ LANGUAGE PLPGSQL; COMMIT; +-- re-enable the triggers we disabled before starting the transaction +ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed; +ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete; +ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901; +ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers; +ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set; + -- Not running changes from example.reporter-extension.sql since these are -- not installed by default, but including a helpful note. \qecho -- 2.43.2