-- This is a maintenance script to update all rows in search_cache.
-- It should be run whenever the search normalization functions in func.sql are updated.

-- This script is intentionally slow and performs the updates in smaller
-- batches in order to avoid long-held locks, which may otherwise cause the
-- site to become unresponsive. It also tries to avoid table bloat by only
-- updating rows that need to be updated.

DO $$
DECLARE
  rows_per_transaction CONSTANT integer := 1000;
  sleep_seconds CONSTANT float := 1;
  i integer;
BEGIN
  -- chars
  FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM chars), rows_per_transaction) x(n)
  LOOP
    PERFORM update_search(vndbid('c', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
    COMMIT;
    PERFORM pg_sleep(sleep_seconds);
  END LOOP;

  -- producers
  FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM producers), rows_per_transaction) x(n)
  LOOP
    PERFORM update_search(vndbid('p', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
    COMMIT;
    PERFORM pg_sleep(sleep_seconds);
  END LOOP;

  -- vn
  FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM vn), rows_per_transaction) x(n)
  LOOP
    PERFORM update_search(vndbid('v', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
    COMMIT;
    PERFORM pg_sleep(sleep_seconds);
  END LOOP;

  -- releases
  FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM releases), rows_per_transaction) x(n)
  LOOP
    PERFORM update_search(vndbid('r', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
    COMMIT;
    PERFORM pg_sleep(sleep_seconds);
  END LOOP;

  -- staff
  FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM staff), rows_per_transaction) x(n)
  LOOP
    PERFORM update_search(vndbid('s', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
    COMMIT;
    PERFORM pg_sleep(sleep_seconds);
  END LOOP;
END$$;

-- These tables are small enough
SELECT count(*) FROM (SELECT update_search(id) FROM tags) x;
SELECT count(*) FROM (SELECT update_search(id) FROM traits) x;

ANALYZE search_cache;