-- This is a maintenance script to update all 'c_search' cache columns. -- 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. -- I don't like how the c_search generated column expressions are repeated in -- this script, but it is what it is. 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 UPDATE chars SET name = name WHERE id BETWEEN vndbid('c', i+1) AND vndbid('c', i+rows_per_transaction) AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n')); 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 UPDATE producers SET name = name WHERE id BETWEEN vndbid('p', i+1) AND vndbid('p', i+rows_per_transaction) AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n')); 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 WITH x(n, s) AS (SELECT id, search_gen_vn(id) FROM vn WHERE id BETWEEN vndbid('v', i+1) AND vndbid('v', i+rows_per_transaction)) UPDATE vn SET c_search = s FROM x WHERE id = n AND c_search IS DISTINCT FROM s; 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 UPDATE releases SET title = title WHERE id BETWEEN vndbid('r', i+1) AND vndbid('r', i+rows_per_transaction) AND c_search IS DISTINCT FROM search_gen(ARRAY[title, original]); COMMIT; PERFORM pg_sleep(sleep_seconds); END LOOP; -- staff_alias FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(aid) FROM staff_alias), rows_per_transaction) x(n) LOOP UPDATE staff_alias SET name = name WHERE aid BETWEEN i+1 AND i+rows_per_transaction AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]); COMMIT; PERFORM pg_sleep(sleep_seconds); END LOOP; END$$; -- These tables are small enough UPDATE tags SET name = name WHERE c_search IS DISTINCT FROM search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n')); UPDATE traits SET name = name WHERE c_search IS DISTINCT FROM search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'));