summaryrefslogtreecommitdiff
path: root/util/updates
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-07-23 16:22:47 +0200
committerYorhel <git@yorhel.nl>2009-07-23 16:22:47 +0200
commit91b95b0bbf17a5756e5a7da4649f82f5a9184975 (patch)
tree03c5d8b88eb150c0043f374c9429b7fce68179a0 /util/updates
parent2ca3c8ed395490090e134883a3a364336fd62d77 (diff)
Implemented relgraph notify from PgSQL trigger
This finishes the new relation graph generator, as it'll now regenerate graphs as soon as is needed. This obsletes the VNDB::Util::Misc::vnCacheUpdate() function, this functionality is provided by triggers within PostgreSQL. The update_vncache(0) procedure is now significantly slower due to the trigger on the vn table. It'd be a good idea to rewrite this procedure by using triggers and conditional updates, to drastically lower the number of rows that need to be updated.
Diffstat (limited to 'util/updates')
-rw-r--r--util/updates/update_2.6.sql36
1 files changed, 36 insertions, 0 deletions
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
index 428c6369..567af268 100644
--- a/util/updates/update_2.6.sql
+++ b/util/updates/update_2.6.sql
@@ -191,3 +191,39 @@ $$ LANGUAGE plpgsql;
CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
+-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
+-- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred)
+-- vn.rgraph is set to NULL when:
+-- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be)
+-- 3. New VN revision of which the title differs from previous revision (deferred)
+-- 4. New VN revision with items in vn_relations that differ from previous revision (deferred)
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF TG_TABLE_NAME = 'vn' THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN
+ IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
+ -- 2.
+ IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ -- 3 & 4
+ IF OLD.latest <> NEW.latest AND (
+ EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
+ OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest)
+ OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest)
+ ) THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+