Re: Triggers and Full Text Search * - Mailing list pgsql-general

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@gmail.com 
> <mailto:esconsult1@gmail.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@visena.com <mailto:andreas@visena.com>> wrote:
> 
>         På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
>         <malik.a.rumi@gmail.com <mailto:malik.a.rumi@gmail.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.klaver@aklaver.com