Re: FTS trigger works 1 at a time, but fails with bulk insert script - Mailing list pgsql-general

On 10/8/18 1:25 PM, Malik Rumi wrote:
> I hope this comes out readable. If not I can do a separate attachment. I 
> notice it says 'BEFORE INSERT'. Maybe that should be after?

No as the return value would be ignored:

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

"The return value of a row-level trigger fired AFTER or a 
statement-level trigger fired BEFORE or AFTER is always ignored; it 
might as well be null. However, any of these types of triggers might 
still abort the entire operation by raising an error."

So what is the script you used to do the bulk INSERT?


> 
> <pre>                                        Table 
> "public.ktab_entry"
>      Column     |           Type           | Collation | Nullable |      
>            Default
> ---------------+--------------------------+-----------+----------+----------------------------------------
>   id            | integer                  |           | not null | 
> nextval('ktab_entry_id_seq'::regclass)
>   title         | character varying(100)   |           | not null |
>   slug          | character varying(100)   |           | not null |
>   content       | text                     |           | not null |
>   posted_date   | timestamp with time zone |           | not null |
>   chron_date    | date                     |           | not null |
>   clock         | time without time zone   |           | not null |
>   category      | character varying(25)    |           | not null |
>   search_vector | tsvector                 |           |          |
>   image1        | character varying(100)   |           |          |
>   image2        | character varying(100)   |           |          |
>   image3        | character varying(100)   |           |          |
> Indexes:
>      "ktab_entry_pkey" PRIMARY KEY, btree (id)
>      "ktab_entry_slug_e1313695_uniq" UNIQUE CONSTRAINT, btree 
> (slug)
>      "ktab_entry_title_6950e951_uniq" UNIQUE CONSTRAINT, btree 
> (title)
>      "ktab_entry_search__d5071f_gin" gin (search_vector)
>      "ktab_entry_slug_e1313695_like" btree (slug 
> varchar_pattern_ops)
>      "ktab_entry_title_6950e951_like" btree (title 
> varchar_pattern_ops)
> Referenced by:
>      TABLE "ktab_entry_tags" CONSTRAINT 
> "ktab_entry_tags_entry_id_294f83f9_fk_ktab_entry_id" FOREIGN 
> KEY (entry_id) REFERENCES ktab_entry(id) DEFERRABLE INITIALLY DEFERRED
> Triggers:
>      search_vector_update BEFORE INSERT OR UPDATE ON ktab_entry FOR EACH 
> ROW EXECUTE PROCEDURE entry_search_vector_trigger()
> </pre>
> 
> <pre><span style="background-color:#FFFFFF"><font color="#300A24">  GNU 
> nano 2.9.3               /tmp/psql.edit.24305.sql                        
>   </font></span>
> 
> <font color="#3465A4">CREATE</font> OR REPLACE <font 
> color="#3465A4">FUNCTION</font> <font 
> color="#EF2929"><b>public</b></font>.<font 
> color="#D3D7CF">entry_search_vector_trigger(</font>)
>   RETURNS trigger
>   <font color="#3465A4">LANGUAGE</font> <font 
> color="#729FCF"><b>plpgsql</b></font>
> <font color="#3465A4">AS</font> $function$            <font 
> color="#3465A4">BEGIN</font>
>                <font color="#3465A4">SELECT</font> <font 
> color="#D3D7CF">setweight(to_tsvector(</font>NEW.title), <font 
> color="#4E9A06">'A'</font>) ||
>                       <font 
> color="#D3D7CF">setweight(to_tsvector(</font>NEW.content), <font 
> color="#4E9A06">'B'</font>) ||
>                       <font 
> color="#D3D7CF">setweight(to_tsvector(</font>NEW.category), <font 
> color="#4E9A06">'D'</font>) ||
>                       <font 
> color="#D3D7CF">setweight(to_tsvector(</font>COALESCE(<font 
> color="#D3D7CF">string_agg(</font>tag.tag, <font color="#4E9A06">', 
> '</font>), $
>                <font color="#3465A4">INTO</font> NEW.search_vector
>                <font color="#3465A4">FROM</font> ktab_entry <font 
> color="#3465A4">AS</font> entry
>                  LEFT JOIN ktab_entry_tags <font 
> color="#3465A4">AS</font> entry_tags ON entry_tags.entry_id $
>                  LEFT JOIN ktab_tag <font color="#3465A4">AS</font> tag 
> ON tag.id <http://tag.id> = entry_tags.tag_id
>                <font color="#3465A4">WHERE</font> entry.id 
> <http://entry.id> = NEW.id
>                <font color="#3465A4">GROUP</font> BY entry.id 
> <http://entry.id>, category;
>                <font color="#75507B">RETURN</font> NEW;
>              <font color="#3465A4">END</font>;
>              $function$
> </pre>
> */“None of you has faith until he loves for his brother or his neighbor 
> what he loves for himself.”/*
> 
> 
> On Mon, Oct 8, 2018 at 2:57 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 10/8/18 12:29 PM, Malik Rumi wrote:
>      > 1. This code is entry_search_vector_trigger(), one of 3 trigger
>      > functions based on the Django model that created the site.
>      > 2. So this is the trigger definition (as far as I know) and it is
>     on the
>      > Entry table. There is also a Tag table and the Tags intersection
>     table.
>      > 3. Uhh, I'm not sure. I assume this is it, that when a new entry is
>      > posted, the function that parses the entry into searchable text and
>      > indexes the words is called. But I can tell you I got this code from
>      > this blog post:
>      > blog.lotech.org/postgres-full-text-search-with-django.html
>     <http://blog.lotech.org/postgres-full-text-search-with-django.html>
>      >
>     <http://blog.lotech.org/postgres-full-text-search-with-django.html>. I
>      > asked the author about this issue. He said he wasn't sure wthout
>      > debugging if it was something he left out or something I did wrong.
>      > 4. Postgresql 9.4. Yea, I know, I should upgrade...
> 
>     Your function name does not match up with the code on the site, so we
>     will need to see the actual trigger/function.
> 
>     In psql do:
> 
>     \d entry
> 
>     to see the trigger definition and then post it here.
> 
>     Also from that definition you can get the function name.
> 
>     Again in psql do:
> 
>     \ef fnc_name
> 
>     to confirm the function is the one you think it is.
> 
>     Would also be helpful to see the script you wrote to do the bulk insert.
> 
>      >
>      > */“None of you has faith until he loves for his brother or his
>     neighbor
>      > what he loves for himself.”/*
>      >
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com