diff options
author | Yorhel <git@yorhel.nl> | 2019-10-07 11:07:57 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-07 11:11:49 +0200 |
commit | 65ed955890c7ee7250a2bce4467c8c092c1bbbbe (patch) | |
tree | 43dda83ad493e4350dff6af301f5c1a5b6b779aa /util/sql | |
parent | ba02e35cea8f2f25ab0ed1a4fccc8983435f9671 (diff) |
SQL: Perform incremental updates on tags_vn_inherit when tags are modified
This has some limitations:
- tags.c_items is not updated, so that may be out of sync.
(The UPDATE takes about 400ms, so doing that more regularly from
Multi::Maintenance should be a viable option)
- When the hidden flag of a VN is changed, the tags will also be out of
sync.
- I don't see a way to do fast incremental updates when tag entries
themselves are changed, e.g. to handle changes in the tag tree or
searchable flag
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 28 |
1 files changed, 19 insertions, 9 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index 57e0b43f..4375dfb3 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -123,12 +123,19 @@ $$ LANGUAGE plpgsql; --- recalculate tags_vn_inherit -CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +-- Recalculate tags_vn_inherit. +-- When a vid is given, only the tags for that vid will be updated. These +-- incremental updates do not affect tags.c_items, so that may still get +-- out-of-sync. +CREATE OR REPLACE FUNCTION tag_vn_calc(uvid integer) RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; - TRUNCATE tags_vn_inherit; - -- populate tags_vn_inherit + IF uvid IS NULL THEN + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; + TRUNCATE tags_vn_inherit; + ELSE + DELETE FROM tags_vn_inherit WHERE vid = uvid; + END IF; + INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler) -- Group votes to generate a list of directly-upvoted (vid, tag) pairs. -- This is essentually the same as the tag listing on VN pages. @@ -138,6 +145,7 @@ BEGIN JOIN tags t ON t.id = tv.tag WHERE NOT tv.ignore AND t.state = 2 AND vid NOT IN(SELECT id FROM vn WHERE hidden) + AND (uvid IS NULL OR vid = uvid) GROUP BY tv.tag, tv.vid HAVING AVG(tv.vote) > 0 -- Add parent tags @@ -155,10 +163,12 @@ BEGIN FROM t_all WHERE tag IN(SELECT id FROM tags WHERE searchable) GROUP BY tag, vid; - -- recreate index - CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); - -- and update the VN count in the tags table - UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + + IF uvid IS NULL THEN + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); + UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + END IF; + RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; |