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:
Does this sleep / open / close / proceed idea seem like a workable solution?
Is there a better workaround?
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.
