Re: Triggers and Full Text Search *

On 4/21/20 11:21 AM, Malik Rumi wrote:
> @Ericson,
> Forgive me for seeming dense, but how does COPY help or hurt here?
>
> @Andreas,
> I had to laugh at your reference to "prose". Would you believe I am
> actually a published playwright? Long before I started coding, of
> course. Old habits die hard.....

The script code via Python/Django/psycopg2 would be helpful as my
suspicion is that you are seeing the effects of open transactions.

>
> entry_search_vector_trigger
>             BEGIN
>               SELECT setweight(to_tsvector(NEW.title), 'A') ||
>                      setweight(to_tsvector(NEW.content), 'B') ||
>                      setweight(to_tsvector(NEW.category), 'D') ||
>                      setweight(to_tsvector(COALESCE(string_agg(tag.tag,
> ', '), '')), 'C')
>               INTO NEW.search_vector
>               FROM ktab_entry AS entry
>                 LEFT JOIN ktab_entry_tags AS entry_tags ON
> entry_tags.entry_id = entry.id <http://entry.id>
>                 LEFT JOIN ktab_tag AS tag ON tag.id <http://tag.id> =
> entry_tags.tag_id
>               WHERE entry.id <http://entry.id> = NEW.id
>               GROUP BY entry.id <http://entry.id>, category;
>               RETURN NEW;
>             END;
>
> tag_search_vector_trigger
>             BEGIN
>               UPDATE ktab_entry SET id = id WHERE id IN (
>                 SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
>               );
>               RETURN NEW;
>             END;
>
> tags_search_vector_trigger
>             BEGIN
>               IF (TG_OP = 'DELETE') THEN
>                 UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
>                 RETURN OLD;
>               ELSE
>                 UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
>                 RETURN NEW;
>               END IF;
>             END;
>
> search_vector_update
>             BEGIN
>               SELECT setweight(to_tsvector(NEW.title), 'A') ||
>                      setweight(to_tsvector(NEW.content), 'B') ||
>                      setweight(to_tsvector(NEW.category), 'D') ||
>                      setweight(to_tsvector(COALESCE(string_agg(tag.tag,
> ', '), '')), 'C')
>               INTO NEW.search_vector
>               FROM ktab_entry AS entry
>                 LEFT JOIN ktab_entry_tags AS entry_tags ON
> entry_tags.entry_id = entry.id <http://entry.id>
>                 LEFT JOIN ktab_tag AS tag ON tag.id <http://tag.id> =
> entry_tags.tag_id
>               WHERE entry.id <http://entry.id> = NEW.id
>               GROUP BY entry.id <http://entry.id>, category;
>               RETURN NEW;
>             END;
>
> search_vector_update  (tags)
>             BEGIN
>               IF (TG_OP = 'DELETE') THEN
>                 UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
>                 RETURN OLD;
>               ELSE
>                 UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
>                 RETURN NEW;
>               END IF;
>             END;
>
> Thank you!
>
>
>
> */“None of you has faith until he loves for his brother or his neighbor
> what he loves for himself.”/*
>
>
> On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith <esconsult1(at)gmail(dot)com
> <mailto:esconsult1(at)gmail(dot)com>> wrote:
>
> I think COPY bypasses the triggers.
>
> Best Regards
> - Ericson Smith
> +1 876-375-9857 (whatsapp)
> +1 646-483-3420 (sms)
>
>
>
> On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh
> <andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>> wrote:
>
> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
> <malik(dot)a(dot)rumi(at)gmail(dot)com <mailto:malik(dot)a(dot)rumi(at)gmail(dot)com>>:
>
> [...]
>
> I am not (yet) posting the trigger code because this post is
> long already, and if your answers are 1) yes, 2) no and 3)
> triggers often work / fail like this, then there’s no point
> and we can wrap this up. But if not, I will happily post
> what I have. Thank you.
>
> This is too much prose for the regular programmer, show us the
> code, and point out what doesn't work for you, then we can help:-)
> --
> Andreas Joseph Krogh
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com