diff options
author | Yorhel <git@yorhel.nl> | 2009-07-23 16:22:47 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-07-23 16:22:47 +0200 |
commit | 91b95b0bbf17a5756e5a7da4649f82f5a9184975 (patch) | |
tree | 03c5d8b88eb150c0043f374c9429b7fce68179a0 /util/updates | |
parent | 2ca3c8ed395490090e134883a3a364336fd62d77 (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.sql | 36 |
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(); + |