devops/postgres/upgrade/0.0.6.9.9.6.4.sql

   1 -- Remove unused old materialized view
   2 drop materialized view context_node_ngrams_view;
   3
   4 -- FIX NGRAMS Parents
   5 with query as (
   6 with child_ngrams as
   7 (select jsonb_array_elements_text(ngrams_repo_element->'children') as term
   8 from node_stories),
   9 parent_ngrams as
  10 (select ngrams_repo_element->>'root' as term
  11 from node_stories)
  12
  13 (select child_ngrams.term, ngrams.terms
  14 from child_ngrams
  15 left join ngrams on child_ngrams.term = ngrams.terms
  16 where ngrams.terms is null
  17
  18 union
  19
  20 select parent_ngrams.term, ngrams.terms
  21 from parent_ngrams
  22 left join ngrams on parent_ngrams.term = ngrams.terms
  23 where ngrams.terms is null
  24 and parent_ngrams.term is not null)
  25
  26 order by term
  27 )
  28 INSERT INTO ngrams (terms) select term from query;
  29
  30
  31 -- ADD triggers
  32  CREATE OR REPLACE FUNCTION check_node_stories_json()
  33   RETURNS TRIGGER AS $$
  34   DECLARE
  35     missing_ngrams_exist boolean;
  36   BEGIN
  37       WITH child_ngrams as
  38         (SELECT jsonb_array_elements_text(NEW.ngrams_repo_element->'children') AS term),
  39         parent_ngrams AS
  40         (SELECT NEW.ngrams_repo_element->>'root' AS term),
  41
  42         ngrams_child_parent AS
  43             (SELECT child_ngrams.term, ngrams.terms
  44          FROM child_ngrams
  45          LEFT JOIN ngrams ON child_ngrams.term = ngrams.terms
  46          WHERE ngrams.terms IS NULL
  47
  48         UNION
  49
  50         SELECT parent_ngrams.term, ngrams.terms
  51         FROM parent_ngrams
  52         LEFT JOIN ngrams ON parent_ngrams.term = ngrams.terms
  53         WHERE ngrams.terms IS NULL
  54         AND parent_ngrams.term IS NOT NULL)
  55
  56             SELECT EXISTS(SELECT * FROM ngrams_child_parent) INTO missing_ngrams_exist;
  57
  58             IF missing_ngrams_exist THEN
  59               RAISE EXCEPTION 'node_stories: ngrams are missing: %', row_to_json(NEW);
  60         END IF;
  61
  62             RETURN NEW;
  63   END;
  64   $$ LANGUAGE plpgsql;
  65
  66 CREATE OR REPLACE TRIGGER check_node_stories_json_trg
  67 AFTER INSERT OR UPDATE
  68 ON node_stories
  69 FOR EACH ROW
  70 EXECUTE PROCEDURE check_node_stories_json();
  71
  72
  73    CREATE OR REPLACE FUNCTION check_ngrams_json()
  74     RETURNS TRIGGER AS $$
  75     DECLARE
  76       missing_ngrams_exist boolean;
  77     BEGIN
  78         WITH child_ngrams as
  79           (SELECT jsonb_array_elements_text(ngrams_repo_element->'children') AS term
  80            FROM node_stories
  81            WHERE term = OLD.terms),
  82           parent_ngrams AS
  83           (SELECT ngrams_repo_element->>'root' AS term
  84            FROM node_stories
  85            WHERE term = OLD.terms),
  86           child_parent_ngrams AS
  87           (SELECT * FROM child_ngrams
  88            UNION SELECT * FROM parent_ngrams)
  89
  90        SELECT EXISTS(SELECT * FROM child_parent_ngrams) INTO missing_ngrams_exist;
  91
  92        IF missing_ngrams_exist THEN
  93           RAISE EXCEPTION 'ngrams are missing: %', row_to_json(OLD);
  94        END IF;
  95
  96        RETURN OLD;
  97     END;
  98     $$ LANGUAGE plpgsql;
  99
 100   CREATE OR REPLACE TRIGGER check_ngrams_json_trg
 101   AFTER DELETE
 102   ON ngrams
 103   FOR EACH ROW
 104   EXECUTE PROCEDURE check_ngrams_json();
 105
 106
 107
 108