summaryrefslogtreecommitdiff
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
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.
-rw-r--r--lib/Multi/RG.pm2
-rw-r--r--lib/VNDB/Handler/Misc.pm2
-rw-r--r--lib/VNDB/Handler/Releases.pm2
-rw-r--r--lib/VNDB/Util/Misc.pm22
-rw-r--r--util/dump.sql36
-rw-r--r--util/updates/update_2.6.sql36
6 files changed, 78 insertions, 22 deletions
diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm
index 303b2e54..65587e93 100644
--- a/lib/Multi/RG.pm
+++ b/lib/Multi/RG.pm
@@ -38,12 +38,14 @@ sub _start {
$_[KERNEL]->alias_set('rg');
$_[KERNEL]->sig(CHLD => 'proc_child');
$_[KERNEL]->sig(shutdown => 'shutdown');
+ $_[KERNEL]->post(pg => listen => relgraph => 'check_rg');
$_[KERNEL]->yield('check_rg');
}
sub shutdown {
$_[KERNEL]->delay('check_rg');
+ $_[KERNEL]->post(pg => unlisten => 'relgraph');
}
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 9ff2d315..a2d346d8 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -323,7 +323,7 @@ sub itemmod {
$self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked}));
# update cached vn info when hiding an r+ page
- $self->vnCacheUpdate(map $_->{vid}, @{$obj->{vn}})
+ $self->dbVNCache(map $_->{vid}, @{$obj->{vn}})
if $type eq 'r' && $act eq 'hide';
$self->resRedirect("/$type$iid", 'temp');
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index cc3f56bc..f2791cb1 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -355,7 +355,7 @@ sub edit {
($rid) = $self->dbReleaseAdd(%opts) if $copy || !$rid;
$self->multiCmd("ircnotify r$rid.$rev");
- $self->vnCacheUpdate(@$new_vn, map $_->{vid}, @$vn);
+ $self->dbVNCache(@$new_vn, map $_->{vid}, @$vn);
return $self->resRedirect("/r$rid.$rev", 'post');
}
diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm
index 139eb571..f4eb5a7c 100644
--- a/lib/VNDB/Util/Misc.pm
+++ b/lib/VNDB/Util/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
use Tie::ShareLite ':lock';
-our @EXPORT = qw|multiCmd vnCacheUpdate|;
+our @EXPORT = qw|multiCmd|;
# Sends a command to Multi
@@ -28,23 +28,5 @@ sub multiCmd {
}
-# Recalculates the vn.c_* columns and regenerates the related relation graphs on any change
-# Arguments: list of vids to be updated
-sub vnCacheUpdate {
- my($self, @vns) = @_;
-
- my $before = $self->dbVNGet(id => \@vns, order => 'v.id', what => 'relations');
- $self->dbVNCache(@vns);
- my $after = $self->dbVNGet(id => \@vns, order => 'v.id');
-
- my @upd = map {
- @{$before->[$_]{relations}} && (
- $before->[$_]{c_released} != $after->[$_]{c_released}
- || $before->[$_]{c_languages} ne $after->[$_]{c_languages}
- ) ? $before->[$_]{id} : ();
- } 0..$#$before;
- $self->multiCmd('relgraph '.join(' ', @upd)) if @upd;
-}
-
-
1;
+
diff --git a/util/dump.sql b/util/dump.sql
index eb502602..84cbecbc 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -737,6 +737,42 @@ $$ 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();
+
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();
+