Re: Triggers and Full Text Search

You may want to send this to pgsql-general(at)postgres(dot)org(dot)
This is a pgAdmin mailing list.

— Ashesh

On Mon, 20 Apr 2020 at 08:11, Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com> wrote:

> More than a year ago, I implemented full text search on one of my sites.
> From the beginning, there was one problem (or at least, what I perceive to
> be a problem): when I use a script to insert many documents at once, they
> do *not* get indexed in fts. If a document is created or inserted one at a
> time, fts indexes immediately. The workaround I came up with was just to
> open each of those script inserted documents and then close them. As soon
> as they are opened, they get indexed.
>
> I assume this has to do with the trigger, which is set to BEFORE, and
> which I carefully followed from the blog post that I got the code from. I
> wrote to that author at the time, but he was of no help. My thought was
> that the trigger was not firing, and thus the documents were not getting
> indexed, because until the document was actually there, there was nothing
> to index. Therefore, I thought a simple switch from BEFORE to AFTER would
> solve my problem. However, in the example in the official docs, BEFORE is
> used as well, so I abandoned that idea and decided to post this question.
>
> Another solution I had in mind was to simply include an additional step in
> my insert script to sleep for one second, during which the current document
> would be opened, and hopefully indexed, and then closed, so the script
> could go on to the next document. Note my insert script is in Python and
> goes through Django. This is not a ‘pure’ postgresql operation, if that
> matters.
>
> My questions are:
>
> 1.
>
> Does this sleep / open / close / proceed idea seem like a workable
> solution?
> 2.
>
> Is there a better workaround?
> 3.
>
> At first blush, I would think the speed of insertion would not be an
> issue for any trigger - it would seem to defeat the purpose - but am I
> wrong about that?
>
>
> 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.
>
> *“None of you has faith until he loves for his brother or his neighbor
> what he loves for himself.”*
>
--

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com>

*http://www.linkedin.com/in/asheshvashi*
<http://www.linkedin.com/in/asheshvashi>