How to recreate tsvector column originally populated by tsvector_update_trigger

Question

I'm running Postgres 9.4.

I have a column called main_search_index_col that was populated by this trigger:

main_vector_update BEFORE INSERT OR UPDATE ON the_entries 
   FOR EACH ROW EXECUTE PROCEDURE 
   svector_update_trigger('main_search_index_col','pg_catalog.english', 'name', 'location', 'type')

I've gone ahead and created a new text search configuration called unaccent_english (public.unaccent_english) to replace pg_catalog.english

I've dropped the above trigger and redone it using public.unaccent_english instead of pg_catalog.english

drop trigger main_vector_update on the_entries;

CREATE TRIGGER main_vector_update BEFORE INSERT OR UPDATE ON the_entries
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger('main_search_index_col','public.unaccent_english', 'name', 'location', 'type');

But as I understand it that won't change any of the values in main_search_index_col unless the row is updated or a new row is added.

I instead want to go through and either update main_search_index_col for each row, or drop the column and recreate it completely and populate it as if the trigger in it's new form had always existed.

How do I make the contents of the column main_search_index_col correct for the new trigger?

Also how do I see the exact definition of tsvector_update_trigger? I have a feeling if I could read the definition of it, it might be helpful in crafting my own solution.

Answer 1

You can find the definition of tsvector_update_trigger in src/backend/utils/adt/tsvector_op.c.

You can update the table to match the new trigger definition as follows:

UPDATE the_entries
   SET main_search_index_col =
       to_tsvector(
          'public.unaccent_english', name || ' ' || location || ' ' || type
       );

But you might as well use the trigger and do it like this:

UPDATE the_entries SET name = name;

That doesn't require inside knowledge of the trigger function definition, and it will also rewrite the whole table.

After such an UPDATE you might want to release the free space on the table with a

VACUUM (FULL, ANALYZE) the_entries;

Note: for recent PostgreSQL versions, a generated column is simpler and better that a column filled by a trigger.