From a26797c4d3e35606fc837241e1e5392498a80b83 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 28 Apr 2008 12:15:25 +0000 Subject: [PATCH] improved concurrency detection git-svn-id: svn://svn.open-ils.org/ILS/trunk@9476 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/reporter-schema.sql | 19 +++++++++++++++---- Open-ILS/src/sql/Pg/upgrade_simple_record.sql | 19 +++++++++++++++---- 2 files changed, 30 insertions(+), 8 deletions(-) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 79e2eeeef8..9fd9a9e77a 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -157,13 +157,24 @@ ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id); CREATE VIEW reporter.super_simple_record AS SELECT * FROM reporter.materialized_simple_record; CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$ +DECLARE + r_id BIGINT; + new_data RECORD; BEGIN - IF TG_OP IN ('UPDATE','DELETE') THEN - DELETE FROM reporter.materialized_simple_record WHERE id = OLD.record; + IF TG_OP IN ('DELETE') THEN + r_id := OLD.record; + ELSE + r_id := NEW.record; END IF; - IF TG_OP IN ('INSERT','UPDATE') AND NOT NEW.deleted THEN - INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.old_super_simple_record WHERE id = NEW.record; + SELECT * INTO new_data FROM reporter.materialized_simple_record WHERE id = r_id FOR UPDATE; + DELETE FROM reporter.materialized_simple_record WHERE id = r_id; + + IF TG_OP IN ('DELETE') THEN + RETURN OLD; + ELSE + INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = NEW.record; + RETURN NEW; END IF; END; diff --git a/Open-ILS/src/sql/Pg/upgrade_simple_record.sql b/Open-ILS/src/sql/Pg/upgrade_simple_record.sql index 8588929d1d..3f87bb0ddc 100644 --- a/Open-ILS/src/sql/Pg/upgrade_simple_record.sql +++ b/Open-ILS/src/sql/Pg/upgrade_simple_record.sql @@ -33,13 +33,24 @@ SELECT r.id, GROUP BY 1,2,3,4,5,6,8,9; CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$ +DECLARE + r_id BIGINT; + new_data RECORD; BEGIN - IF TG_OP IN ('UPDATE','DELETE') THEN - DELETE FROM reporter.materialized_simple_record WHERE id = OLD.record; + IF TG_OP IN ('DELETE') THEN + r_id := OLD.record; + ELSE + r_id := NEW.record; END IF; - IF TG_OP IN ('INSERT','UPDATE') AND NOT NEW.deleted THEN - INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.old_super_simple_record WHERE id = NEW.record; + SELECT * INTO new_data FROM reporter.materialized_simple_record WHERE id = r_id FOR UPDATE; + DELETE FROM reporter.materialized_simple_record WHERE id = r_id; + + IF TG_OP IN ('DELETE') THEN + RETURN OLD; + ELSE + INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = NEW.record; + RETURN NEW; END IF; END; -- 2.43.2