summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-12-05 15:19:32 +0100
committerYorhel <git@yorhel.nl>2009-12-05 15:19:32 +0100
commitc67155961f1903a5e7c6c99b84cae08b7046a1a3 (patch)
treef567b2dc94502354ee878f26a42b2ab1d2b06ecd
parent7554f305ae27091ccff6a692180a5af7282df766 (diff)
SQL: Removed the use of CONSTRAINT TRIGGERs
This makes use of the change that the [vn|producers].latest columns are now only updated after the entire revision has been inserted.
-rw-r--r--ChangeLog1
-rw-r--r--util/dump.sql64
-rw-r--r--util/updates/update_2.10.sql67
3 files changed, 102 insertions, 30 deletions
diff --git a/ChangeLog b/ChangeLog
index 2c5ad423..ec99ce6e 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -8,6 +8,7 @@ git - ?
- Removed /g/debug
- Replaced recursive stored procedures with WITH .. SELECT queries
- Merged db[VN|Producer|Release][Edit|Add] into dbItemEdit and dbItemAdd
+ - Removed the use of CONSTRAINT TRIGGERs
2.9 - 2009-11-16
- Fixed another bug with the calculation of tags_vn_bayesian.spoiler
diff --git a/util/dump.sql b/util/dump.sql
index cf8821ec..6376857a 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -669,70 +669,74 @@ CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots F
-- 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)
+-- 1. NOTIFY is sent on VN edit or insert or change in vn.rgraph, when rgraph = NULL and entries in vn_relations
-- 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)
+-- 2. UPDATE on vn where c_released or c_languages has changed
+-- 3. VN edit of which the title differs from previous revision
+-- 4. VN edit with items in vn_relations that differ from previous
CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
BEGIN
-- 1.
- IF TG_TABLE_NAME = 'vn' THEN
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest 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
+ IF NEW.rgraph IS NOT NULL THEN
+ IF
-- 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 (
+ OLD.c_released IS DISTINCT FROM NEW.c_released
+ OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
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;
+ -- 4. (not-really-readable method of comparing two query results)
+ OR EXISTS(SELECT vid2, relation FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation FROM vn_relations WHERE vid1 = NEW.latest)
+ OR (SELECT COUNT(*) FROM vn_relations WHERE vid1 = OLD.latest) <> (SELECT COUNT(*) FROM vn_relations WHERE vid1 = NEW.latest)
+ )
+ THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
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();
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
-- Same as above for producers, with slight differences in the steps:
-- There is no 2, and
--- 3 = New producer revision of which the name, language or type differs from the previous revision (deferred)
-CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
+-- 3 = Producer edit of which the name, language or type differs from the previous revision
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
BEGIN
-- 1.
- IF TG_TABLE_NAME = 'producers' THEN
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
NOTIFY relgraph;
END IF;
END IF;
- IF TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN
- IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
- -- 3 & 4
- IF OLD.latest <> NEW.latest AND (
+ IF NEW.rgraph IS NOT NULL THEN
+ IF
+ -- 2.
+ OLD.c_released IS DISTINCT FROM NEW.c_released
+ OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
+ -- 4. (not-really-readable method of comparing two query results)
OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
- OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest)
- ) THEN
- UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
- END IF;
+ OR (SELECT COUNT(*) FROM producers_relations WHERE pid1 = OLD.latest) <> (SELECT COUNT(*) FROM producers_relations WHERE pid1 = NEW.latest)
+ )
+ THEN
+ UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON producers DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
-- NOTIFY on insert into changes/posts/tags
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
index 469cce49..a1606591 100644
--- a/util/updates/update_2.10.sql
+++ b/util/updates/update_2.10.sql
@@ -57,3 +57,70 @@ DROP FUNCTION tag_vn_childs() CASCADE;
DROP FUNCTION tag_tree(integer, integer, boolean);
DROP TYPE tag_tree_item;
+
+
+-- improved relgraph notify triggers
+DROP TRIGGER vn_relgraph_notify ON vn;
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest 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 NEW.rgraph IS NOT NULL THEN
+ IF
+ -- 2.
+ OLD.c_released IS DISTINCT FROM NEW.c_released
+ OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
+ 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)
+ -- 4. (not-really-readable method of comparing two query results)
+ OR EXISTS(SELECT vid2, relation FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation FROM vn_relations WHERE vid1 = NEW.latest)
+ OR (SELECT COUNT(*) FROM vn_relations WHERE vid1 = OLD.latest) <> (SELECT COUNT(*) FROM vn_relations WHERE vid1 = NEW.latest)
+ )
+ THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+
+
+DROP TRIGGER vn_relgraph_notify ON producers;
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF NEW.rgraph IS NOT NULL THEN
+ IF
+ -- 2.
+ OLD.c_released IS DISTINCT FROM NEW.c_released
+ OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
+ EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
+ -- 4. (not-really-readable method of comparing two query results)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
+ OR (SELECT COUNT(*) FROM producers_relations WHERE pid1 = OLD.latest) <> (SELECT COUNT(*) FROM producers_relations WHERE pid1 = NEW.latest)
+ )
+ THEN
+ UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+
+