Search Postgresql Archives

Re: Triggers and Full Text Search *

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx <mailto:esconsult1@xxxxxxxxx>> 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@xxxxxxxxxx <mailto:andreas@xxxxxxxxxx>> wrote:

        På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi
        <malik.a.rumi@xxxxxxxxx <mailto:malik.a.rumi@xxxxxxxxx>>:

            [...]

            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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux