From da32af390a1cadddfdf5357f125cad4f9f67d93d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 16 Oct 2018 15:42:31 -0400 Subject: [PATCH] JBAS-2117 Aged money split purge to separate script Move the purging steps, where we delete rows from money.payment / money.billing, to a separate DB update so it can be run separately if necessary. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/aged-billings-payments.sql | 76 +---------------------- KCLS/sql/schema/deploy/aged-money-purge.sql | 76 +++++++++++++++++++++++ KCLS/sql/schema/revert/aged-money-purge.sql | 7 +++ KCLS/sql/schema/sqitch.plan | 1 + 4 files changed, 87 insertions(+), 73 deletions(-) create mode 100644 KCLS/sql/schema/deploy/aged-money-purge.sql create mode 100644 KCLS/sql/schema/revert/aged-money-purge.sql diff --git a/KCLS/sql/schema/deploy/aged-billings-payments.sql b/KCLS/sql/schema/deploy/aged-billings-payments.sql index efa4f57e38..b5ef7726a5 100644 --- a/KCLS/sql/schema/deploy/aged-billings-payments.sql +++ b/KCLS/sql/schema/deploy/aged-billings-payments.sql @@ -72,89 +72,19 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; -SELECT CLOCK_TIMESTAMP(), 'Building money.aged_payment'; +SELECT CLOCK_TIMESTAMP(), 'Populating money.aged_payment'; INSERT INTO money.aged_payment SELECT mp.* FROM money.payment_view mp JOIN action.aged_circulation circ ON (circ.id = mp.xact); -SELECT CLOCK_TIMESTAMP(), 'Building money.aged_billing'; +SELECT CLOCK_TIMESTAMP(), 'Populating money.aged_billing'; INSERT INTO money.aged_billing SELECT mb.* FROM money.billing mb JOIN action.aged_circulation circ ON (circ.id = mb.xact); --- Clean up payments first so account_adjustments won't stick --- around referencing deleted billings - -SELECT CLOCK_TIMESTAMP(), 'Disabling payment triggers'; - -ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr; - -SELECT CLOCK_TIMESTAMP(), 'Deleting payments'; - -DELETE FROM money.payment WHERE id IN ( - SELECT mp.id FROM money.payment mp - JOIN money.aged_payment USING (id) -); - -SELECT CLOCK_TIMESTAMP(), 'Enabling payment triggers'; - -ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr; - -SELECT CLOCK_TIMESTAMP(), 'Creating tmp billing table'; - --- Copy all money.billing rows that are not represented in money.aged_billing -CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS - SELECT mb.* FROM money.billing mb - LEFT JOIN money.aged_billing mab USING (id) - WHERE mab.id IS NULL; - -SELECT CLOCK_TIMESTAMP(), 'Truncating money.billing'; - -ALTER TABLE money.billing DISABLE TRIGGER ALL; - --- drop and rebuild the billing foreign key constraint. --- needed for truncate -ALTER TABLE money.account_adjustment - DROP CONSTRAINT account_adjustment_billing_fkey; - -TRUNCATE money.billing; - -SELECT CLOCK_TIMESTAMP(), 'Rebuilding money.billing'; - -INSERT INTO money.billing SELECT * FROM tmp_money_billing; - -SELECT CLOCK_TIMESTAMP(), 'Done rebuilding money.billing'; - -ALTER TABLE money.billing ENABLE TRIGGER ALL; -ALTER TABLE money.account_adjustment - ADD CONSTRAINT account_adjustment_billing_fkey - FOREIGN KEY (billing) REFERENCES money.billing (id); +SELECT CLOCK_TIMESTAMP(), 'Done populating aged payment/billing tables'; COMMIT; -SELECT CLOCK_TIMESTAMP(), 'Analyzing money.billing'; - -ANALYZE money.billing; - -SELECT CLOCK_TIMESTAMP(), 'Analyzing payment tables'; - -VACUUM ANALYZE money.credit_card_payment; -VACUUM ANALYZE money.forgive_payment; -VACUUM ANALYZE money.cash_payment; - -SELECT CLOCK_TIMESTAMP(), 'All Done'; - diff --git a/KCLS/sql/schema/deploy/aged-money-purge.sql b/KCLS/sql/schema/deploy/aged-money-purge.sql new file mode 100644 index 0000000000..f348b89591 --- /dev/null +++ b/KCLS/sql/schema/deploy/aged-money-purge.sql @@ -0,0 +1,76 @@ +-- Deploy kcls-evergreen:aged-money-data-migration to pg +-- requires: lost-paid-receipts-data + +BEGIN; + +SET STATEMENT_TIMEOUT = 0; + +SELECT CLOCK_TIMESTAMP(), 'Purging aged payments/billings from main tables'; + +ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr; + +SELECT CLOCK_TIMESTAMP(), 'Deleting payments'; + +-- delete all payments that are represented in the aged_payment table +DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment); + +SELECT CLOCK_TIMESTAMP(), 'Enabling payment triggers'; + +ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr; + +SELECT CLOCK_TIMESTAMP(), 'Creating tmp billing table'; + +-- Copy all money.billing rows that are not represented in money.aged_billing +CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS + SELECT mb.* FROM money.billing mb + LEFT JOIN money.aged_billing mab USING (id) + WHERE mab.id IS NULL; + +SELECT CLOCK_TIMESTAMP(), 'Truncating money.billing'; + +ALTER TABLE money.billing DISABLE TRIGGER ALL; + +-- drop and rebuild the billing foreign key constraint. +-- needed for truncate +ALTER TABLE money.account_adjustment + DROP CONSTRAINT account_adjustment_billing_fkey; + +TRUNCATE money.billing; + +SELECT CLOCK_TIMESTAMP(), 'Rebuilding money.billing'; + +INSERT INTO money.billing SELECT * FROM tmp_money_billing; + +SELECT CLOCK_TIMESTAMP(), 'Done rebuilding money.billing'; + +ALTER TABLE money.billing ENABLE TRIGGER ALL; +ALTER TABLE money.account_adjustment + ADD CONSTRAINT account_adjustment_billing_fkey + FOREIGN KEY (billing) REFERENCES money.billing (id); + +COMMIT; + +SELECT CLOCK_TIMESTAMP(), 'Analyzing money.billing'; + +ANALYZE money.billing; + +SELECT CLOCK_TIMESTAMP(), 'Analyzing payment tables'; + +VACUUM ANALYZE money.credit_card_payment; +VACUUM ANALYZE money.forgive_payment; +VACUUM ANALYZE money.cash_payment; + +SELECT CLOCK_TIMESTAMP(), 'All Done'; + diff --git a/KCLS/sql/schema/revert/aged-money-purge.sql b/KCLS/sql/schema/revert/aged-money-purge.sql new file mode 100644 index 0000000000..e4b6b6edc6 --- /dev/null +++ b/KCLS/sql/schema/revert/aged-money-purge.sql @@ -0,0 +1,7 @@ +-- Revert kcls-evergreen:aged-money-data-migration from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 1755823b92..ad0dfc7fa9 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -76,6 +76,7 @@ aged-billings-payments [ecard-notice-validator] 2018-09-24T18:00:57Z Bill Ericks all-circs-slim-backport [aged-billings-payments] 2018-02-08T15:09:53Z Bill Erickson,,, # Backport slim all circs view lost-paid-receipts [all-circs-slim-backport] 2017-07-03T20:10:59Z Bill Erickson,,, # Lost/Paid tracking and receipts lost-paid-receipts-data [lost-paid-receipts] 2017-08-02T15:28:08Z Bill Erickson,,, # Lost/Paid permissions and receipts +aged-money-purge [lost-paid-receipts-data] 2018-10-16T19:33:11Z Bill Erickson,,, # aged billing/payment migration stock-browse-schema [ecard-notice-validator] 2018-08-31T15:22:58Z Bill Erickson,,, # Recover stock browse data tables, etc. stock-browse-headings-report [stock-browse-schema] 2018-10-04T15:56:18Z Bill Erickson,,, # New heading report updates for stock browse stock-browse-cleanup [stock-browse-schema] 2018-10-03T18:05:49Z Bill Erickson,,, # Delete old browse data -- 2.11.0