2

I have a tsv column on my campaign table and I also added a trigger to update it every time the row changes. Tsv is currently constructed from a few table columns. The table also has multiple tags via many-to-many relation. My question is, is it possible to write such a trigger that would also pull in all the tags on row INSERT and update the tsv column when tags are added or removed from a campaign? I already know how to construct a concat document in a query (eg: Full text search on multiple joined tables). I imagine this could work if I wrote another trigger on the many-to-many intermediate table that would update campaigns tsv or something like that.

Edit: This is what I came up with so far and it seems to work but I would like to get an opinion about the whole thing. I have triggers for campaign INSERT, campaign column name UPDATE, INSERT/UPDATE on campaign_tags and one for campaign_tags DELETE. Each one updates the campaign.tsv but in slightly different way.

ALTER TABLE campaigns ADD COLUMN tsv tsvector;

CREATE INDEX campaigns_tsv_idx ON newsletters USING GIN (tsv);

campaigns_tsv_update

CREATE OR REPLACE FUNCTION campaigns_tsv_update() RETURNS trigger AS $$
begin
  SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') 
           INTO new.tsv 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=new.id
           GROUP BY campaigns.id;
  RAISE NOTICE 'campaigns_tsv_trigger new is %', new;
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_update ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_update BEFORE UPDATE OF name
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_update();

campaigns_tsv_insert

CREATE OR REPLACE FUNCTION campaigns_tsv_insert() RETURNS trigger AS $$
begin
  new.tsv := setweight(to_tsvector(unaccent(coalesce(new.name, ' '))), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_insert ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_insert BEFORE INSERT
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_insert();

campaigns_tags_tsv_update

CREATE OR REPLACE FUNCTION campaign_tags_tsv_update() RETURNS trigger AS $$
begin

UPDATE campaigns SET tsv=subquery.document FROM (
    SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') AS document 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=new.campaign_id
           GROUP BY campaigns.id
) AS subquery WHERE campaigns.id=new.campaign_id;
RAISE NOTICE 'campaign_tags_tsv_update new is %', new;

return new;

end;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tags_tsv_trigger ON campaign_tags;
CREATE TRIGGER campaigns_tags_tsv_trigger AFTER INSERT OR UPDATE
    ON campaign_tags FOR EACH ROW EXECUTE PROCEDURE campaign_tags_tsv_update();

campaigns_tags_tsv_update_del

CREATE OR REPLACE FUNCTION campaign_tags_tsv_update_del() RETURNS trigger AS $$
begin

UPDATE campaigns SET tsv=subquery.document FROM (
    SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') AS document 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=old.campaign_id
           GROUP BY campaigns.id
) AS subquery WHERE campaigns.id=old.campaign_id;
RAISE NOTICE 'campaign_tags_tsv_update_del old is %', old;

return old;

end;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tags_tsv_trigger_old ON campaign_tags;
CREATE TRIGGER campaigns_tags_tsv_trigger_old AFTER DELETE
    ON campaign_tags FOR EACH ROW EXECUTE PROCEDURE campaign_tags_tsv_update_del();
2
+50

This is what I came up with so far and it seems to work but I would like to get an opinion about the whole thing.

Incoming.. opinion. Don't use triggers for this. It's wayyy to complex. You have three piece of functionality that will make life easier.

  1. Function indexes.
  2. Views.
  3. Materialized views: write a query that generates the output table that you want to query simply issue REFRESH MATERIALIZED VIEW mymatview; to update it. How often do you have to reflect tag updates anyway? A materialized view can itself use function indexes, or any other kind of index. For instance, you can create a Materialized View of the tsvectors of multiple columns from multiple tables, and then write a function index that uses native functionality over those multiple tsvector columns. Then when you want, periodically run REFRESH MATERIALIZED VIEW mymatview.

You may also want to restructure how you're doing tags. In PG, you can easily represent them on the table itself with an array type which is easily indexed.

Update

This code just strikes me as awkward. We call this a DEFAULT. Don't implement that as a trigger.

CREATE OR REPLACE FUNCTION campaigns_tsv_insert() RETURNS trigger AS $$
begin
  new.tsv := setweight(to_tsvector(unaccent(coalesce(new.name, ' '))), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_insert ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_insert BEFORE INSERT
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_insert();

Instead

ALTER TABLE campaigns ADD COLUMN tsv tsvector DEFAULT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A');
  • 1. So, just adding an index on all columns that are part of the tsv? – cen Nov 8 '16 at 8:07
  • 2. As per documentation, I decided to go with a seperate tsv column because it's faster and I can rely on default_text_search_config. If I understand correctly, using a view would mean that ts vectors need to be constructed for every query call while my approach already has all of them ready to go. The docs do mention that this might not be as important speed wise but it'd be interesting to know if there are some benchmarks about preconstructed TSV versus computed TSVs. – cen Nov 8 '16 at 8:07
  • 3. If I add or remove a tag from a campaign I want this to be reflected in the search immediatelly, I can't afford periodical refreshing. The reason tags are a seperate entity is because I want to keep as much code as possible database agnostic so I wouldn't use PG arrays. I do agree however that my trigger approach appears to be overcomplicated. – cen Nov 8 '16 at 8:07
  • When you're using plpgsql, what's a specialized array type? – Evan Carroll Nov 8 '16 at 14:55
  • If you are asking, why not use array type if I already use custom Postgres full text search? Basically, search is just a minor functionality in my codebase so it can either be axed or simplified if really necessary. I want to keep my model db agnostic as much as possible, especially for basic functionality. FTS is an extra thing which can also be ported to ES, it just happens to already exist in Postgres so I decided to use it instead of adding yet another dependency. And even if I do that, I guess the question still applies as a general scenario. Thanks for the update, that makes sense. – cen Nov 9 '16 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.